Part 2: Migration Notes from MySQL to Postgresql Using pgloader

This is a multi-part series starting with Part 1.

After perfecting the schema and data migration with pgloader rules and getting the application sessions and login code working, it was time to convert the non-working SQL statements to Postgres syntax.

Here’s the SQL syntax I had to change (note: I use placeholders (?) whenever possible):

MySQL Postgresql 9.1 Notes/PG 9.5
UNIX_TIMESTAMP(?) FLOOR(EXTRACT(EPOCH FROM ?))
FROM_UNIXTIME(?) TO_TIMESTAMP(?)
DATE_ADD(?, INTERVAL ? unit) ? + INTERVAL ‘$x timeunit’ Perl DBI can do placeholder with ?::interval and “$x timeunit”
DATE_SUB(?, INTERVAL ? unit) ? – INTERVAL ‘$x timeunit’ see above for interval placeholder info
? = 0 or ‘0000-00-00 00:00:00’ ? IS NULL
RAND() RANDOM()
INSERT IGNORE INSERT in the case when the action is not important. PG 9.5: INSERT … ON DUPLICATE KEY IGNORE
REPLACE INTO custom code in the case when rows are immutable (never updated). PG 9.5: INSERT … ON DUPLICATE KEY UPDATE
IF() CASE WHEN … THEN … ELSE … END
LIMIT ?, ? LIMIT ? OFFSET ? can use the pg syntax for both databases 🙂
GROUP BY GROUP BY Postgresql is stricter about the columns list here
ORDER BY NULL MySQL optimization only
SQL_CALC_ … FOUND_ROWS SELECT COUNT(*) MySQL optimization only
DATE(?) TO_CHAR(?, ‘YYYY-MM-DD’)
HOUR(?) EXTRACT(HOUR FROM ?)
table `user` table users Postgresql has public.user, so treat the user table name as a reserved symbol
UPDATE table1, table2 … UPDATE table1 … WHERE id IN (SELECT id FROM table2 WHERE …)
last insert id (various) RETURNING

Don’t forget to:

  • ensure string lengths don’t exceed your schema column widths, and that Postgresql char() space-padding isn’t an issue for your application code
  • verify how Postgresql dates use timezones
  • run EXPLAIN on each of your statements. 🙂

Perl CGI::Session Notes

pgloader migrates the MySQL CGI::Session table using a Postgresql text column. This works better:

alter table sessions alter a_session type bytea using a_session::bytea;
This entry was posted in Open Source, Postgresql, Tech. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.