0

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?

  • 1
    sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION (default in MySQL 8.0.19) takes care of many niggles with GROUP BY &c. One feature I would like to see in PostgreSQL is the UNSIGNED qualifier. MySQL has better GENERATED AS functionality with STORED and VIRTUAL - PG only has STORED (for now). Apart from minor niggles, PostgreSQL is vastly superior on many levels to MySQL - JSON support, INTERVAL types, PostGIS inter alia! – Vérace Mar 18 at 20:46
  • I just noticed - (answering another question) that PostgreSQL allows you to use a GENERATED field as a PRIMARY KEY whereas MySQL does not - interesting if you have some sort of non-linear function which you want to use - bit of an edge case but interesing none-the-less! As an aside (coincidentally also with the word "generate") is the generate_series function - so simple, so powerful! – Vérace Mar 20 at 11:20
  • 1
    Apologies for all the comments (last one!), but if you want a good site as a supplement to @a_horse_with_no_name 's answer on SO, you could look here. – Vérace Mar 20 at 12:32
0

One other Gotcha to watch out for - the innocent-looking "group by".

PostgreSQL, like most sensible DBMSs, does "group by" properly, insisting that everything that you select must be either "grouped by" or be aggregated through a function, like sum().

MySQL can be configured so that it does not.

In this state, you can do [silly] things like this:

select 
  `date` 
, min( invoice_num ) 
, flavour 
from table1 
group by `date` ; 

What value should be returned in the "flavour" column? I have no idea and nor does PostgreSQL, which is why it won't allow this. MySQL can be perfectly happy with this, handing you back any, arbitrary value of flavour, rather than throwing an error.

| improve this answer | |
  • Thanks Phill, While good to know, I don't view this as a PostgreSQL deficiency. – user1032531 Mar 18 at 15:38
  • @user1032531: note that this would work in Postgres if DATE is defined as the primary (or unique) key – a_horse_with_no_name Mar 18 at 15:53
  • SQLite first was MySQL compatible, and then answered this question. ("If it's silly and a customer pays you for it, it isn't silly.") – CL. Mar 20 at 10:42
0

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

| improve this answer | |
  • Thanks horse with no name, Looks like MySQL's IF() function is another. Nice list of things to look out for. While your post on Stackoverflow is helpful, it primarily focuses on PostgreSQL features which are not supported by MySQL. – user1032531 Mar 18 at 15:46
  • @user1032531: I didn't mention IF as it is so far from the SQL standard, that I thought that was obvious. – a_horse_with_no_name Mar 18 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.