Close
Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

PIVOTAL LABS
No really, you have to pick *one*

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?).

Comments
  1. Joseph Palermo says:

    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.

  2. Joe Van Dyk says:

    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

  3. Joe Van Dyk says:

    Here it is in action: https://gist.github.com/1407737

    So, I think the answer is really: “Upgrade to postgresql 9.1.” :-)

  4. Joe Van Dyk says:

    Of course, this assumes that you are grouping by something unique.

  5. Joe Van Dyk says:

    Oops, you have to group by the primary key. For whatever reason, grouping by something unique and not null doesn’t work.

  6. Alex says:

    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

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *