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

3 files changed, 47 insertions, 11 deletions

diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index a326960ff4d..2736868fb06 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1145,10 +1145,36 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 
    <para>
     In strict SQL, <literal>GROUP BY</literal> can only group by columns of
-    the source table but <productname>PostgreSQL</productname> extends
+    the source table, but <productname>PostgreSQL</productname> extends
     this to also allow <literal>GROUP BY</literal> to group by columns in the
     select list.  Grouping by value expressions instead of simple
-    column names is also allowed.
+    column names is also allowed (but <literal>GROUP BY</literal>
+    expressions cannot contain aggregate functions or window functions).
+   </para>
+
+   <para>
+    PostgreSQL also supports the syntax <literal>GROUP BY ALL</literal>,
+    which is equivalent to explicitly writing all select-list entries that
+    do not contain either an aggregate function or a window function.
+    This can greatly simplify ad-hoc exploration of data.
+    As an example, these queries are equivalent:
+<screen>
+<prompt>=&gt;</prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY ALL;</userinput>
+ a | b | ?column? | sum
+---+---+----------+----
+ 1 | 4 |        5 |  9
+ 2 | 5 |        7 | 12
+ 3 | 6 |        9 | 15
+(3 rows)
+
+<prompt>=&gt;</prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY a, b, a + b;</userinput>
+ a | b | ?column? | sum
+---+---+----------+----
+ 1 | 4 |        5 |  9
+ 2 | 5 |        7 | 12
+ 3 | 6 |        9 | 15
+(3 rows)
+</screen>
    </para>
 
    <indexterm>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index d7089eac0be..5a3bcff7607 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -37,7 +37,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ { * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
     [ WHERE <replaceable class="parameter">condition</replaceable> ]
-    [ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
+    [ GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } ]
     [ HAVING <replaceable class="parameter">condition</replaceable> ]
     [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
     [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
@@ -796,7 +796,7 @@ WHERE <replaceable class="parameter">condition</replaceable>
    <para>
     The optional <literal>GROUP BY</literal> clause has the general form
 <synopsis>
-GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...]
+GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] }
 </synopsis>
    </para>
 
@@ -808,21 +808,31 @@ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</rep
     <replaceable class="parameter">grouping_element</replaceable>
     can be an input column name, or the name or ordinal number of an
     output column (<command>SELECT</command> list item), or an arbitrary
-    expression formed from input-column values.  In case of ambiguity,
+    expression formed from input-column values; however, it cannot contain
+    an aggregate function or a window function.  In case of ambiguity,
     a <literal>GROUP BY</literal> name will be interpreted as an
     input-column name rather than an output column name.
    </para>
 
    <para>
+    The form <literal>GROUP BY ALL</literal> with no explicit
+    <replaceable class="parameter">grouping_elements</replaceable>
+    provided is equivalent to writing <literal>GROUP BY</literal> with the
+    numbers of all <command>SELECT</command> output columns that do not
+    contain either an aggregate function or a window function.
+   </para>
+
+   <para>
     If any of <literal>GROUPING SETS</literal>, <literal>ROLLUP</literal> or
     <literal>CUBE</literal> are present as grouping elements, then the
     <literal>GROUP BY</literal> clause as a whole defines some number of
     independent <replaceable>grouping sets</replaceable>.  The effect of this is
     equivalent to constructing a <literal>UNION ALL</literal> between
-    subqueries with the individual grouping sets as their
+    subqueries having the individual grouping sets as their
     <literal>GROUP BY</literal> clauses.  The optional <literal>DISTINCT</literal>
-    clause removes duplicate sets before processing; it does <emphasis>not</emphasis>
-    transform the <literal>UNION ALL</literal> into a <literal>UNION DISTINCT</literal>.
+    key word removes duplicate grouping sets before processing; it does <emphasis>not</emphasis>
+    transform the implied <literal>UNION ALL</literal> into
+    a <literal>UNION DISTINCT</literal>.
     For further details on the handling
     of grouping sets see <xref linkend="queries-grouping-sets"/>.
    </para>
diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml
index ae7e6bed24f..233f9bfa284 100644
--- a/doc/src/sgml/ref/select_into.sgml
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -27,15 +27,15 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] <replaceable class="parameter">new_table</replaceable>
     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
     [ WHERE <replaceable class="parameter">condition</replaceable> ]
-    [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
+    [ GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } ]
     [ HAVING <replaceable class="parameter">condition</replaceable> ]
     [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
     [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
     [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
-    [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
-    [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
+    [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
+    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
 </synopsis>
  </refsynopsisdiv>