Giampiero Raschetti <Giampiero.Raschetti@popso.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
