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

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! ;)