Eager loading and group by in PostgreSQL

Question

I know PostgreSQL, unlike MySQL, requires to list all selected fields in the group by clause when using aggregate functions, e.g.

Transaction.select('sum(amount), category_id').group('category_id')

Fair enough. But when I try to eager load an association, e.g.

Transaction.select('sum(amount), categories.name').includes(:category).group('categories.name')

it doesn't work because you haven't included all fields of both models in the group by clause.

Is there a way to avoid having to list all fields of the models, or should I consider accepting the N+1 queries? (I don't think listing 30 fields makes sense when I only need 2...)

Answer 1

As of new versions of PostgreSQL (9.1+ if I recall correctly) you can group by the primary keys of the tables only. Previous versions (and most RDBMSs as well) require that you provide all columns. The key-only exception works because we know all attributes are functionally dependent on the key so if the key changes, the other attributes will be distinct anyway.

MySQL is an exception, as Frank pointed out in the comments above. This is responsible for many non-deterministic aspects of that database in cases like this. Non-deterministic is generally bad and to be avoided.