From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: postgres-hape@gmx.de
Subject: group by can use alias from select list
Date: Fri, 07 Jul 2023 07:36:00 +0000
Message-ID: <168871536004.379168.9352636188330923805@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-select.html
Description:
hi
i have a discussion in pgsql-bugs@lists.postgresql.org about this and it is
mentioned in the answers that it is documented and a "feature" of Postgres
that this can be done.
If this is wanted the documentation
(https://www.postgresql.org/docs/current/sql-select.html) that mentioned:
"
Description
SELECT retrieves rows from zero or more tables. The general processing of
SELECT is as follows:
1.) All queries in the WITH list are computed. These effectively serve as
temporary tables that can be referenced in the FROM list. A WITH query that
is referenced more than once in FROM is computed only once, unless specified
otherwise with NOT MATERIALIZED. (See WITH Clause below.)
2.) All elements in the FROM list are computed. (Each element in the FROM
list is a real or virtual table.) If more than one element is specified in
the FROM list, they are cross-joined together. (See FROM Clause below.)
3.) If the WHERE clause is specified, all rows that do not satisfy the
condition are eliminated from the output. (See WHERE Clause below.)
4.) If the GROUP BY clause is specified, or if there are aggregate function
calls, the output is combined into groups of rows that match on one or more
values, and the results of aggregate functions are computed. If the HAVING
clause is present, it eliminates groups that do not satisfy the given
condition. (See GROUP BY Clause and HAVING Clause below.)
5.) The actual output rows are computed using the SELECT output expressions
for each selected row or row group. (See SELECT List below.)
"
isn't correct because how can 4.) be done and the alias from 5.) is used?
Here is a hint important that there is an exception for alias used in group
by
the SQL for that:
with tbase(id)
as (select 1 union all select 2)
select id otto from tbase
group by otto
order by otto
what do you think about that?
hape
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, postgres-hape@gmx.de
Subject: Re: group by can use alias from select list
In-Reply-To: <168871536004.379168.9352636188330923805@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox