Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples.
TL;DR: Most MySQL types have an obvious PostgreSQL twin, but eight need a decision: TINYINT(1) , UNSIGNED integers, DATETIME vs TIMESTAMP , ENUM , SET , ON UPDATE CURRENT_TIMESTAMP , AUTO_INCREMENT and the collation behind every unique text column. The last one fails silently: a MySQL unique email refuses [email protected] after [email protected] , and the same constraint on PostgreSQL accepts both.
Converting a MySQL schema to PostgreSQL is mostly mechanical, and the parts that are not mechanical are the ones that break production. Eight kinds of column need a decision, because the obvious PostgreSQL type either refuses data MySQL accepted, or accepts data MySQL refused.…