Migrating from MySQL to PostgreSQL: Grouping and Ordering

index.txt

Table structure:

test_id                                         place_id
9b949b1a-414b-43f0-8940-9b821f1e2098            a3358c22-af08-485f-933e-f4d6ce900882
5177ab63-5ecd-4241-913e-36fb9d917efd            a3358c22-af08-485f-933e-f4d6ce900882
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e            a3358c22-af08-485f-933e-f4d6ce900882

MySQL: SELECT test_id, place_id FROM transmissions GROUP BY place_id

Result (CORRECT):

test_id                                         place_id
9b949b1a-414b-43f0-8940-9b821f1e2098            a3358c22-af08-485f-933e-f4d6ce900882

PostgreSQL:
SELECT test_id, place_id FROM transmissions
GROUP BY place_id

Error: [42803] ERROR: column "transmissions.test_id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8

Attempt:
SELECT test_id, place_id FROM transmissions
GROUP BY place_id, test_id

Result (WRONG):

test_id                                         place_id
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e            a3358c22-af08-485f-933e-f4d6ce900882
5177ab63-5ecd-4241-913e-36fb9d917efd            a3358c22-af08-485f-933e-f4d6ce900882
9b949b1a-414b-43f0-8940-9b821f1e2098            a3358c22-af08-485f-933e-f4d6ce900882

Workaround:

SELECT
    DISTINCT ON (place_id) test_id, place_id
FROM transmissions

Result (CORRECT):

test_id                                         place_id
9b949b1a-414b-43f0-8940-9b821f1e2098            a3358c22-af08-485f-933e-f4d6ce900882

What if I wanted?

MySQL: SELECT test_id, place_id FROM tests GROUP BY place_id ORDER BY test_id ASC

Result:

test_id                                         place_id
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e            a3358c22-af08-485f-933e-f4d6ce900882

PostgreSQL:
SELECT
    DISTINCT ON (place_id) test_id, place_id
FROM transmissions
ORDER BY test_id ASC

Error: [42P10] ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Position: 25

Attempt:

SELECT
    DISTINCT ON (place_id, test_id) test_id, place_id
FROM transmissions
ORDER BY test_id ASC, place_id

Result (WRONG):

test_id                                         place_id
5177ab63-5ecd-4241-913e-36fb9d917efd            a3358c22-af08-485f-933e-f4d6ce900882
c16ac09c-8ccf-45b9-8593-7ffceb4d5d9e            a3358c22-af08-485f-933e-f4d6ce900882
9b949b1a-414b-43f0-8940-9b821f1e2098            a3358c22-af08-485f-933e-f4d6ce900882

Came from: https://twitter.com/webdevilopers/status/1477974865911193606

As a partial explanation of why what worked in MySQL doesn't directly work in PgSQL (or other DBs):

MySQL allowed some loose queries when using GROUP BY. You can make it stricter using the ONLY_FULL_GROUP_BY sql_mode (which is - or should be, if your distro doesn't mess with the defaults - enabled by default on new installs).

See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

I also recommend reading up on the other sql_mode values and making sure you're at least using the 5.7+ default sql_mode. These can highlight other issues (data corruption through value truncation), especially when developing using languages such as PHP which ignore MySQL's warnings.

Thanks for your feedback @AllenJB ! I will recommend those MySQL settings to our DBA. Before migrating to PgSQL he should "fix" those queries.

It feels a little bit like introducing "strict mode" and "type hinting" to legacy code in PHP! ;)