Peter Eisentraut <pete...@gmx.net> writes:
> Apparently, you can write this (an attempt at a convenient workaround
> for lack of functional dependency tracking pre-9.1):
> SELECT pg_class.* FROM pg_class GROUP BY pg_class.*;
> It won't work:
> ERROR: 42803: column "pg_class.relname" must appear in the GROUP BY clause
> or be used in an aggregate function
I haven't traced through the code, but I think what is happening is that
the GROUP BY is interpreted like GROUP BY ROW(pg_class.x, pg_class.y, ...)
which doesn't guarantee uniqueness of the individual columns.
The star notation in the target list is interpreted differently: it
results in an explicit expansion into separate Var references. And then
those fail the grouping check since they're not forced unique by the
GROUP BY clause.
> Is there any rhyme or reason for this? I couldn't find anything about
> this in the documentation or in the SQL standard. I guess the whole
> thing is inconsistent all over the place; I'd just like to verify that
> the current behavior is somewhat intentional.
The behavior in the target list is mandated by SQL spec, for sure.
But I doubt that the spec defines the above GROUP BY syntax at all.
I wouldn't claim that the current behavior in GROUP BY is "intentional"
--- it's not a case that I ever thought about, anyway. Not sure how
practical it would be to change that.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
