Re: GROUP BY ALL

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

> Andrey Borodin <amborodin86(at)gmail(dot)com> writes:
> > I saw a thread in a social network[0] about GROUP BY ALL. The idea seems
> 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 (…field1, field2, …) as t from
…tables…) 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.