ActiveRecord Error with PostgreSQL but not SQLite - GroupingError - column must appear in the GROUP BY clause or be used in an aggregate function

Question

This ActiveRecord query works in SQLite:

SlotReq.group(:team_id)

In PostgreSQL, the same query gives this error "GroupingError - column slot_reqs.id must appear in the GROUP BY clause or be used in an aggregate function"

Based on the answer to this question I changed my query to:

SlotReq.select("slot_reqs.team_id").group("slot_reqs.team_id")

and it works as expected.

I would like to know if I'm doing it right and why does this work?

is team_id unique in the table? Which version of PostgreSQL are you using? — coorasse, Jul 10 '17 at 05:07
This answer may make things clear. https://stackoverflow.com/a/18061451/7573791 — Sajin, Jul 10 '17 at 05:33

score 2 · Accepted Answer · answered Jul 10 '17 at 05:11

Yes, you are doing it right, although you could also use:

SlotReq.select(:team_id).group(:team_id)

What happens is that PG (among other DB's) needs that all column names in SELECT must be either aggregated or used in GROUP BY clause; this is because, if any unagreggated column is omitted, it could lead to indeterminate behavior (i.e. What value should be used in that column?)

So, by specifying in select just the column you want to group, you don't omit any column; on the other hand, using group withoutselect, is equivalent to doing SELECT * FROM table GROUP BY column, which brings all columns but only one being specified on GROUP BY clause.

Replaced my query with yours using symbols. Looks much better to me. Thanks for your explanation. — RajeshM, Jul 10 '17 at 16:43

ActiveRecord Error with PostgreSQL but not SQLite - GroupingError - column must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1