hape Hape <postgres-hape@gmx.de> writes:
> works (how does the group by know about otto if the sequence is done as
> described above?)
This is intentional and documented, eg
https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY
says
An expression used inside a grouping_element can be an input column
name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values.
whereas the discussion of HAVING does not mention allowing output
columns. The reason for this is mainly that it'd seldom be useful
for a HAVING expression to be just a bare column reference, but
as soon as you write something that's not a bare column reference,
the output-column special case is disallowed anyway.
Yes, it's messy. That's mostly because we are still trying to
preserve some compatibility with SQL92, which did things differently
from later standards in this area. But it's been like that for a
couple of decades now, and we're not going to change it.
regards, tom lane