From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: postgres-hape@gmx.de
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: group by can use alias from select list
Date: Thu, 7 Sep 2023 10:11:00 -0400
Message-ID: <ZPnZ9ILOZcWaqoY0@momjian.us> (raw)
In-Reply-To: <1591985.1694044890@sss.pgh.pa.us>
References: <168871536004.379168.9352636188330923805@wrigleys.postgresql.org>
<25b70e6ec7bef81acac508dc61dfa01e72c96252.camel@cybertec.at>
<CAKFQuwYALfM-yamV7GCPG_WZvi_qK5O8rJPOGkD40ShDd9M_GQ@mail.gmail.com>
<ZPkQhuV8I6ijH+y3@momjian.us>
<1591985.1694044890@sss.pgh.pa.us>
On Wed, Sep 6, 2023 at 08:01:30PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Tue, Jul 11, 2023 at 07:31:26AM -0700, David G. Johnston wrote:
> >> I think the complaint is that someone seeing the behavior in the wild comes to
> >> this order-of-operations and doesn't see that the observed behavior is
> >> documented.
>
> > How is the attached patch?
>
> Maybe better "Although query output columns are nominally computed in the
> next step, they can also be referenced (by name or by ordinal number)
> as <literal>GROUP BY</literal> elements".
I modified the text slightly in the attached patch.
> You could go further and add "Such references cannot be parts of
> <literal>GROUP BY</literal> expressions, however." Not sure if we
> cover that explicitly anywhere else.
We do farther down the docs in the GROUP BY section:
https://www.postgresql.org/docs/devel/sql-select.html
GROUP BY will condense into a single row all selected rows that share
the same values for the grouped expressions. 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. In case of ambiguity, a
------------------------
GROUP BY name will be interpreted as an input-column name rather than an
output column name.
It says
* input column name
* output column name or ordinal number
* expression formed from input-column values
Is this too subtle?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] group-by.diff (765B, ../ZPnZ9ILOZcWaqoY0@momjian.us/2-group-by.diff)
download | inline diff:diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgmlindex 0ee0cc7e64..974153cb78 100644--- a/doc/src/sgml/ref/select.sgml+++ b/doc/src/sgml/ref/select.sgml@@ -130,7 +130,10 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
If the <literal>HAVING</literal> clause is present, it
eliminates groups that do not satisfy the given condition. (See
<xref linkend="sql-groupby"/> and
- <xref linkend="sql-having"/> below.)+ <xref linkend="sql-having"/> below.) + Although query output columns are nominally computed in the next+ step, they can also be referenced (by name or ordinal number)+ in the <literal>GROUP BY</literal> clause.
</para>
</listitem>
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: bruce@momjian.us, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, laurenz.albe@cybertec.at, postgres-hape@gmx.de, pgsql-docs@lists.postgresql.org
Subject: Re: group by can use alias from select list
In-Reply-To: <ZPnZ9ILOZcWaqoY0@momjian.us>
* 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