Mysql – What queries are supported by MySQL but not PostgreSQL

MySQLpostgresql

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:

  1. 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).
  2. PostgreSQL has no INSERT … ON DUPLICATE KEY UPDATE ("upsert")
  3. 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).
  4. GROUP BY … ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
  5. 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.
  6. 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?

Best Answer

re 1) very often they can be replaced with other features (e.g. I have seen query variables being used to simulate window functions or pass the result from one query to another)

re 2) Postgres has insert ... on conflict()

re 3) Postgres has logical replication

re 4) Postgres has GROUP BY ROLLUP

re 5) That's true. Other alternatives are pg_cron or pg_timetable

re 6) I have worked more (and longer) with Oracle than I have worked with Postgres. And I can count the number of times I needed a table with a clustered index (called "index organized table" in Oracle) on my two hands in the last 10 years. So I don't really miss them in Postgres (although I do agree it would come in handy sometimes).

What other queries or features would one need to change?

In general, queries that are invalid (standard) SQL.

You already noticed that Postgres complies with the SQL standard and uses || as the string concatenation operator, not for OR).

Integer division is different. 1/10 yields 0 in Postgres (because both arguments are integers) whereas MySQL returns a decimal value.

Then there is the invalid use of GROUP BY, invalid use of MySQL's interpretation of "boolean" expressions, invalid DATE values (2020-02-31or 0000-00-00), Postgres also does not allow to divide by zero.

Postgres is much stricter when it comes to matching datatypes (varchar_column = 1 will fail), while MySQL usually lets you get away with comparing apples to oranges.

Non-standard quoting using those dreaded backticks ` (in general do not quote identifiers at all).

Accept that a "database" in MySQL is really a schema (as seen by Postgres and the SQL standard). If you have multiple databases in MySQL you most probably want a single database with multiple schemas in Postgres.

There are other syntax differences, e.g. date arithmetic works differently in MySQL and Postgres, including interval literals. Many functions have different names (string_agg() vs. group_concat()).

Obviously several data types are different as well (e.g. the infamous int(11))

P.S.: I maintain a more up-to-date feature comparison in this answer

Related Question