column "users.name" must appear in the GROUP BY clause or be used in an aggregate function

Laravel app, just moved from MySQL to PostgreSQL. The full message as Laravel shows it:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "users.name" must appear in the GROUP BY clause or be used in an aggregate function

Why it happens

MySQL (with ONLY_FULL_GROUP_BY off) lets you select columns that are not grouped and returns a value from an arbitrary row. Postgres refuses. Grouping by the table's primary key is allowed, so groupBy('users.id') with users.* works.

Fix

-- pick one: SELECT team_id, MAX(name) FROM users GROUP BY team_id; -- aggregate it SELECT DISTINCT ON (team_id) team_id, name FROM users ORDER BY team_id, id DESC; -- latest row per group

Worth knowing: the old MySQL query may have been returning the wrong row the whole time. In our test, select * ... group by user_id on non-strict MariaDB 11.8 returned the oldest row, not the latest.

The tested fix for every table at once (scripts, a Laravel command, before/after checks): Laravel Postgres Error Fix Pack ($19), item 03.

Several errors at once? Paste your log into the error matcher. Raw SQL with MySQL syntax: the free SQL translator. Not migrated yet: the free schema risk check finds this kind of problem before the switch. Planning the whole move: Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5).

Other errors after MySQL to Postgres

Published with deed.page