In process of migrating to PostgreSQL and am looking for a list of types of queries I must change (or some online query converter should one exist). What originally prompted this is my attempt to execute
LEFT OUTER JOIN t ON t.id=t1.id || t.id=t2.id || t.id=t3.id which works with MySQL but not PostgreSQL (turns out I must use
OR instead of
|| and I expect the same goes for
https://dba.stackexchange.com/a/60254/95143 primarily lists PostgreSQL features which are not supported by MySQL and may be out of date (last updated 3 years ago), however, might list some still relevent:
- There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
- PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
- At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
- GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
- PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.
- Things that MySQL has (but not Postgres) are clustered indexes, GROUP BY .. ROLLUP and the Event (scheduler). – (From ypercube's comment)
Many of these I don't care about, however, I will definitely need to address
ON DUPLICATE KEY UPDATE and take a close look at PostgreSQL's reserved words. What other queries or features would one need to change?