Re: [SQL] PgSQL 6.5.1 and Group BY pb

Giampiero Raschetti <Giampiero(dot)Raschetti(at)popso(dot)it> writes:
> And now the output query with GROUP BY:

> template1=> SELECT g.nome,u.uid,u.id FROM gruppi g, usergroup u GROUP BY
> g.nome;
> ERROR: Illegal use of aggregates or non-group column in target list
> template1=> SELECT nome,id FROM gruppi GROUP BY nome;
> ERROR: Illegal use of aggregates or non-group column in target list

That is not a bug, it is the way things are supposed to work --- you
have variables in your SELECT that are not being grouped over, and are
not inside an aggregate function, so they have no well-defined value.

Postgres before 6.5 was not very good about catching this error all
the time, but 6.5 includes a more complete error check.

> And now the output query with SELECT DISTINCT:

> template1=> SELECT DISTINCT ON g.nome g.nome,u.uid,u.id from gruppi g,
> usergroup u;
> ERROR: parser: parse error at or near "."

It looks like DISTINCT ON wants a plain unqualified column name.

DISTINCT ON is not standard SQL, and IMHO we ought to take it out ---
it's not well-defined. It's got the same definitional problem as
non-group-by columns: what values of the other variables are you going
to get?

regards, tom lane