Skip to content

Instantly share code, notes, and snippets.

@webdevilopers

webdevilopers/index.txt

Last active January 4, 2022 07:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save webdevilopers/06617b75c77f0499f8c8bffc49284648 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save webdevilopers/06617b75c77f0499f8c8bffc49284648 to your computer and use it in GitHub Desktop.
Download ZIP
Migrating from MySQL to PostgreSQL: Grouping and Ordering
Raw
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
@webdevilopers
Copy link
Author

webdevilopers commented Jan 3, 2022

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

@AllenJB
Copy link

AllenJB commented Jan 3, 2022

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.

@webdevilopers
Copy link
Author

webdevilopers commented Jan 4, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment