From: Tom Lane <tgl@sss.pgh.pa.us>
To: hape Hape <postgres-hape@gmx.de>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: group by can use alias from select list but not the having clause
In-reply-to: 
 <trinity-c2f74081-7c6f-4b9c-b4cc-67c301118083-1688377401497@3c-app-gmx-bs14>
References: 
 <trinity-c2f74081-7c6f-4b9c-b4cc-67c301118083-1688377401497@3c-app-gmx-bs14>
Comments: In-reply-to hape Hape <postgres-hape@gmx.de>
	message dated "Mon, 03 Jul 2023 11:43:21 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <345993.1688393531.1@sss.pgh.pa.us>
Date: Mon, 03 Jul 2023 10:12:11 -0400
Message-ID: <345994.1688393531@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/345994.1688393531%40sss.pgh.pa.us>
Precedence: bulk

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