Why no "SELECT foo.* ... GROUP BY foo.id" in Postgres?

Question

I have a query like this:

select foo.*, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

This worked great with SQLite and MySQL. Postgres however, complains about me not including all columns of foo in the group by clause. Why is this? Isn't it enough that foo.id is unique?

Answer 1

Just in case other people stumble over this question:

Starting with PostgreSQL 9.1 it's sufficient to list the columns of the primary key in the group by clause (so the example from the question would work now).

Answer 2

Some databases are more relaxed about this, for good and bad. The query is unspecific, so the result is equally unspecific. If the database allows the query, it will return one record from each group and it won't care which one. Other databases are more specific, and require you to specify which value you want from the group. They won't let you write a query that has an unspecific result.

The only values that you can select without an aggregate is the ones in the group by clause:

select foo.id, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

You can use aggregates to get other values:

select foo.id, min(foo.price), count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id

If you want all the values from the foo table, you can either put them all in the group by clause (if that gives the correct result):

select foo.id, foo.price, foo.name, foo.address, count(bar.id)
from foo inner join bar on foo.id = bar.foo_id
group by foo.id, foo.price, foo.name, foo.address

Or, you can join the table with a subquery:

select foo.id, foo.price, foo.name, foo.address, sub.bar_count
from foo
inner join (
   select foo.id, bar_count = count(bar.id)
   from foo inner join bar on foo.id = bar.foo_id
   group by foo.id
) sub on sub.id = foo.id