Ask for Help
“Postgres SELECT * GROUP BY insists that all selected fields must either appear in the GROUP BY or be aggregated.”
Many agreed that this is, in fact, how a database should behave and that MySQL’s leniency on this matter is faulty. If you select a field that you don’t group by, you must tell Postgres how to combine the sub-set of values that fall in a given group into a single result (should it take the max? the min? the mean? the most purple?).
Yes, MySQL is incorrect from a logical perspective, but it is decidedly more convenient.
If I am grouping by users.id and also selecting users.*, other databases force me to also group by every column in the users table. This is not only inconvenient, but will also break as soon as the schema for the users table changes.
If I could group by users.*, it would be better, but you can’t do that.
I don’t know how much extra work it is, but I’d imagine grouping by a single column and then picking the first record for every other column is going to be a lot faster than grouping by a dozen or so columns. Databases may detect that and optimize for it though.
If you really dislike the MySQL behavior, you can always enable the ONLY_FULL_GROUP_BY option, which makes it behave like everybody else. Personally though, MySQL’s behavior has never caused me a problem, and probably saved me a bit of pain and grief.
November 29, 2011 at 11:17 am
This changed in 9.1, I think.
SELECT count(*), entity_name, entity_address as address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
count | entity_name | address
——-+————-+———-
2 | HR | address1
2 | SALES | address2
November 29, 2011 at 2:02 pm
Here it is in action: https://gist.github.com/1407737
So, I think the answer is really: “Upgrade to postgresql 9.1.” :-)
November 29, 2011 at 6:41 pm
Of course, this assumes that you are grouping by something unique.
November 29, 2011 at 6:52 pm
Oops, you have to group by the primary key. For whatever reason, grouping by something unique and not null doesn’t work.
November 29, 2011 at 7:08 pm
If you don’t need to support mysql in app, you should use window function for such grouping.
http://www.postgresql.org/docs/9.1/static/tutorial-window.html
December 1, 2011 at 12:42 am