PostgreSQL for MySQL developers

/ home / computer / postgresql / postgresql-for-mysql-admins / .

DML - Befehle

• Pep Pla, Percona, 2025-11-14: A Tale of Two Databases: No-Op Updates in PostgreSQL and MySQL
• Shiv Iyer, MinervaDB, 2025-11-21: How PostgreSQL and MySQL Handle No-Op Updates

PostgreSQL Transactions and MVCC

MySQL isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

PostgreSQL isoaltion levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE

The standard says SERIALIZABLE should be default.

Sources:

• Transaction Isolation
• SET TRANSACTION

Migration to PostgreSQL

MySQL to PostgreSQL migration problems we faced:

DML and other issues

• INSERT IGNORE does NOT exist ➜ use just INSERT instead.
• Watch for character set / collation handling differences (C locale vs. ICU vs. …)

Data types

• TINYINT does NOT exist ➜ SMALLINT
• BLOB does NOT exist ➜ BYTEA
• UNSIGNED does NOT exist ➜ use signed with possibly next bigger data type?
• DOUBLE is called ➜ DOUBLE PRECISION in PostgreSQL
• DATETIME does NOT exist ➜ TIMESTAMP

DDL syntax differences

• Different syntax for protected keywords: \user\ ➜ "user"
• AUTO_INCREMENT INT does NOT exist ➜ use SERIAL
• ) ENGINE = InnoDB does NOT exist ➜ just remove it
• PostgreSQL has different protected keywords than MySQL, for example: end ➜ "end"
• Prefixed indexes: bla(42) ➜ substring(bla, 1, 42)
• Inline create index does NOT exist in CREATE TABLE statement ➜ use CREATE INDEX ... ON ... (...); instead.
• CREATE TABLE ... LIKE ... is different ➜ use CREATE TABLE ... (LIKE ... INCLUDING ALL); instead.

Other aid

• Check for PostgreSQL migrations tools.
• Check for AI migration support.
• See also here: https://www.shinguz.ch/computer/postgresql/interesting-postgresql-blog-articles/#migration

LISTEN / NOTIFY

• LISTEN
• NOTIFY
• Jimmy Angelakos, 2026-07-15: LISTEN Carefully: How NOTIFY Can Trip Up Your Database

TRIGGER

• Christophe Pettus, PGX, 2026-07-15: All Your GUCs in a Row: event_triggers

Temporary Tables

https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-TEMPORARY

UNLOGGED Tables

https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED

PostgreSQL Tuning

1. PostgreSQL Tuning
   • Transaction handling
   • Temporary tables
   • Stored Procedures
   • Schema tuning
     • Data models / normalization
     • Data types
     • Indexing
   • Query tuning
   • Profiling
   • Performance views