MIME-Version: 1.0
References: 
 <CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com>
 <835590.1671424214@sss.pgh.pa.us>
In-Reply-To: <835590.1671424214@sss.pgh.pa.us>
From: Isaac Morland <isaac.morland@gmail.com>
Date: Mon, 19 Dec 2022 08:44:39 -0500
Message-ID: 
 <CAMsGm5f5USbSRugipvbcsQ-bgVicDOgspY97gv8Y1eOWT26rVQ@mail.gmail.com>
Subject: Re: GROUP BY ALL
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrey Borodin <amborodin86@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5f9ff05f02e8505"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMsGm5f5USbSRugipvbcsQ-bgVicDOgspY97gv8Y1eOWT26rVQ%40mail.gmail.com>
Precedence: bulk

--000000000000f5f9ff05f02e8505
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, 18 Dec 2022 at 23:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andrey Borodin <amborodin86@gmail.com> writes:
> > I saw a thread in a social network[0] about GROUP BY ALL. The idea seem=
s
> useful.
>
> Isn't that just a nonstandard spelling of SELECT DISTINCT?
>

In a pure relational system, yes; but since Postgres allows duplicate rows,
both in actual table data and in intermediate and final result sets, no.
Although I'm pretty sure no aggregates other than count() are useful - any
other aggregate would always just combine count() copies of the duplicated
value in some way.

What would happen if there are aggregate functions in the tlist?
> I'm not especially on board with "ALL" meaning "ALL (oh, but not
> aggregates)".
>

The requested behaviour can be accomplished by an invocation something like=
:

select (t).*, count(*) from (select (=E2=80=A6field1, field2, =E2=80=A6) as=
 t from
=E2=80=A6tables=E2=80=A6) s group by t;

So we collect all the required fields as a tuple, group by the tuple, and
then unpack it into separate columns in the outer query.

--000000000000f5f9ff05f02e8505
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, 18 Dec 2022 at 23:30, Tom Lane &l=
t;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>=
</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;bor=
der-left-color:rgb(204,204,204);padding-left:1ex">Andrey Borodin &lt;<a hre=
f=3D"mailto:amborodin86@gmail.com" target=3D"_blank">amborodin86@gmail.com<=
/a>&gt; writes:<br>
&gt; I saw a thread in a social network[0] about GROUP BY ALL. The idea see=
ms useful.<br>
<br>
Isn&#39;t that just a nonstandard spelling of SELECT DISTINCT?<br></blockqu=
ote><div><br></div><div>In a pure relational system, yes; but since Postgre=
s allows duplicate rows, both in actual table data and in intermediate and =
final result sets, no. Although I&#39;m pretty sure no aggregates other tha=
n count() are=C2=A0useful - any other aggregate would always just combine c=
ount() copies of the duplicated value in some way.</div><div><br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);paddi=
ng-left:1ex">
What would happen if there are aggregate functions in the tlist?<br>
I&#39;m not especially on board with &quot;ALL&quot; meaning &quot;ALL (oh,=
 but not<br>
aggregates)&quot;.<br>
</blockquote><div><br></div><div>The requested behaviour can be accomplishe=
d by an invocation something like:</div><div><br></div><div>select (t).*, c=
ount(*) from (select (=E2=80=A6field1, field2, =E2=80=A6) as t from =E2=80=
=A6tables=E2=80=A6) s group by t;</div><div><br></div><div>So we collect al=
l the required fields as a tuple, group by the tuple, and then unpack it in=
to separate columns in the outer query.</div></div></div>

--000000000000f5f9ff05f02e8505--