Menu

1 / 3

MySQL to PostgreSQL Schema Conversion: What Each Column Type Should Become, and What Silently Changes

DEV Community: postgres·Son Tran·19 days ago
#bhfFYGAY
#devtopostgresql#devto#dev#mysql#column
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.