On 29th of September 2025, Tom Lane committed patch:
Add GROUP BY ALL. GROUP BY ALL is a form of GROUP BY that adds any TargetExpr that does not contain an aggregate or window function into the groupClause of the query, making it exactly equivalent to specifying those same expressions in an explicit GROUP BY list. This feature is useful for certain kinds of data exploration. It's already present in some other DBMSes, and the SQL committee recently accepted it into the standard, so we can be reasonably confident in the syntax being stable. We do have to invent part of the semantics, as the standard doesn't allow for expressions in GROUP BY, so they haven't specified what to do with window functions. We assume that those should be treated like aggregates, i.e., left out of the constructed GROUP BY list. In passing, wordsmith some existing documentation about GROUP BY, and update some neglected synopsis entries in select_into.sgml. Author: David Christensen <david@pgguru.net> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com
To be fair, I'm not sure I would be using it, but it's a new feature, it's been accepted to sql standard, so it is worth mentioning.
Example is really simple:
=$ select relnamespace, relkind, count(*) from pg_class group by all; relnamespace │ relkind │ count ──────────────┼─────────┼─────── 99 │ t │ 48 13331 │ v │ 65 11 │ i │ 124 13331 │ r │ 4 99 │ i │ 48 11 │ v │ 81 2200 │ r │ 13 2200 │ i │ 10 11 │ r │ 64 2200 │ S │ 3 (10 rows)
Previously this query would have to be written like this:
=$ select relnamespace, relkind, count(*) from pg_class group by relnamespace, relkind;
A bit more verbose, and explicit, but doing the same thing.
New syntax, group by all will make Pg treat this query as if it had, in group by clause, every column that isn't aggregate or window function (and doesn't include them).
In my case, since count(*) is an aggregate, it wouldn't be part of this “generated" group by clause, but both relnamespace and relkind would.
This will also work, of course, with joins, or more complicated expressions, like:
=$ select n.nspname, c.relkind, count(*) filter ( where c.relname ~ 'a' ), array_agg(distinct relowner::regrole::text) from pg_class c join pg_namespace n on c.relnamespace = n.oid group by all order by n.nspname, c.relkind; nspname | relkind | count | array_agg --------------------+---------+-------+---------------- information_schema | r | 3 | {pgdba} information_schema | v | 51 | {pgdba} pg_catalog | i | 98 | {pgdba} pg_catalog | r | 41 | {pgdba} pg_catalog | v | 68 | {pgdba} pg_toast | i | 48 | {depesz,pgdba} pg_toast | t | 48 | {depesz,pgdba} public | S | 1 | {depesz} public | i | 2 | {depesz} public | r | 3 | {depesz} (10 rows)
Looks useful, thanks to everyone involved 🙂