The Migration Checklist
What actually bites people during a migration, grouped by theme
Data Types & Schema
- UTF-8 all the way through // PostgreSQL speaks Unicode real well.
- How to reset AUTO_INCREMENT in MySQL? // PostgreSQL sequences, bigserial and identity columns
- Which MySQL data type to use for storing boolean values? // PostgreSQL has a boolean data type for you
- How does MySQL's ENUM type migrate to PostgreSQL? // MySQL declares ENUM inline on the column; PostgreSQL needs a separate CREATE TYPE. pgloader does the translation automatically.
- Should I use field 'datetime' or 'timestamp'? // And what about the time zone, too?
- How do I handle MySQL's 0000-00-00 zero dates in PostgreSQL? // MySQL allows an all-zero date as a value; PostgreSQL doesn't. Here's the pgloader transform that fixes it during migration.
Query & Syntax Differences
- Why did my queries break after migrating — is PostgreSQL case-sensitive? // myTable and mytable are the same table in MySQL, and two different things in PostgreSQL. The most common silent breakage after cutover.
- What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? // SQL basics explained
- Can I concatenate multiple MySQL rows into one field? // Yes sure, that's called an AGGREGATE.
- What's the PostgreSQL equivalent of MySQL's LAST_INSERT_ID()? // There's no LAST_INSERT_ID() function in PostgreSQL — use RETURNING to get the new id back from the same INSERT statement.
- How to output MySQL query results in CSV format? // PostgreSQL COPY and psql output formats.
- How to import an SQL file using the command line in MySQL? // The psql include facility, and the COPY command and protocol.
Security & Access
- How can I prevent SQL injection in PHP? // Little Bobby Tables is fun and all that, but still…
- How to get a list of MySQL user accounts? // First steps using psql and catalog queries
Or jump straight to a category
Data Types & Schema
Query & Syntax Differences
- Why did my queries break after migrating — is PostgreSQL case-sensitive?
- What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
- Can I concatenate multiple MySQL rows into one field?
- What's the PostgreSQL equivalent of MySQL's LAST_INSERT_ID()?
- How to output MySQL query results in CSV format?
- How to import an SQL file using the command line in MySQL?
Browse every question
UTF-8 all the way through
PostgreSQL speaks Unicode real well.
How to reset AUTO_INCREMENT in MySQL?
PostgreSQL sequences, bigserial and identity columns
Which MySQL data type to use for storing boolean values?
PostgreSQL has a boolean data type for you
Why did my queries break after migrating — is PostgreSQL case-sensitive?
myTable and mytable are the same table in MySQL, and two different things in PostgreSQL. The most common silent breakage after cutover.
How do I handle MySQL's 0000-00-00 zero dates in PostgreSQL?
MySQL allows an all-zero date as a value; PostgreSQL doesn't. Here's the pgloader transform that fixes it during migration.
How does MySQL's ENUM type migrate to PostgreSQL?
MySQL declares ENUM inline on the column; PostgreSQL needs a separate CREATE TYPE. pgloader does the translation automatically.
What's the PostgreSQL equivalent of MySQL's LAST_INSERT_ID()?
There's no LAST_INSERT_ID() function in PostgreSQL — use RETURNING to get the new id back from the same INSERT statement.
Should I use field 'datetime' or 'timestamp'?
And what about the time zone, too?
How to output MySQL query results in CSV format?
PostgreSQL COPY and psql output formats.
How to import an SQL file using the command line in MySQL?
The psql include facility, and the COPY command and protocol.
Can I concatenate multiple MySQL rows into one field?
Yes sure, that's called an AGGREGATE.
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
SQL basics explained
How can I prevent SQL injection in PHP?
Little Bobby Tables is fun and all that, but still…
How to get a list of MySQL user accounts?
First steps using psql and catalog queries
Ready to leave MySQL behind for good?
This is one piece of a bigger method — pgloader, pg_auto_failover, and the stored-procedure porting no tool automates for you — built for developers making exactly this move.
Get the MySQL → PostgreSQL Course