On Thu, Oct 26, 2023 at 03:09:26PM -0700, David G. Johnston wrote:
> On Thu, Oct 26, 2023 at 2:56 PM Bruce Momjian <[email protected]> wrote:
>
> On Wed, Oct 25, 2023 at 10:34:10PM -0700, David G. Johnston wrote:
> > I would reword the existing note to be something like:
> >
> > The SQL Standard defines specific aggregates and their properties,
> including
> > which of DISTINCT and/or ORDER BY is allowed. Due to the extensible
> nature of
> > PostgreSQL it accepts either or both clauses for any aggregate.
>
> Uh, is this something in my patch or somewhere else? I don't think
> PostgreSQL extensible is an example of syntax flexibility.
>
>
> https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
>
> Note
> The ability to specify both DISTINCT and ORDER BY in an aggregate function is
> a
> PostgreSQL extension.
>
> I am pointing out that the first sentence of the existing note above seems to
> be factually incorrect. I tried to make it correct - while explaining why we
> differ. Though in truth I'd probably rather just remove the note.
Agreed, removed, patch attached. This is just too complex to specify.
> > We get enough complaints regarding "apparent ordering" that I would like
> to
> > add:
> >
> > As a reminder, while some DISTINCT processing algorithms produce sorted
> output
> > as a side-effect, only by specifying ORDER BY is the output order
> guaranteed.
>
> Well, we need to create a new email thread for this and look at all the
> areas is applies to since this is a much larger issue.
>
> I was hoping to sneak this one in regardless of the bigger picture issues,
> since this specific combination is guaranteed to output ordered presently.
No sneaking. ;-) It would be bad to document this unevenly because it
sets expectations in other parts of the system if we don't mention it.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7c3e940afe..3b49e63987 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20273,6 +20273,13 @@ SELECT NULLIF(value, '(none)') ...
aggregation.
</para>
+ <para>
+ While all aggregates below accept an optional
+ <literal>ORDER BY</literal> clause (as outlined in <xref
+ linkend="syntax-aggregates"/>), the clause has only been added to
+ aggregates whose output is affected by ordering.
+ </para>
+
<table id="functions-aggregate-table">
<title>General-Purpose Aggregate Functions</title>
<tgroup cols="2">
@@ -20310,7 +20317,7 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>array_agg</primary>
</indexterm>
- <function>array_agg</function> ( <type>anynonarray</type> )
+ <function>array_agg</function> ( <type>anynonarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>anyarray</returnvalue>
</para>
<para>
@@ -20321,7 +20328,7 @@ SELECT NULLIF(value, '(none)') ...
<row>
<entry role="func_table_entry"><para role="func_signature">
- <function>array_agg</function> ( <type>anyarray</type> )
+ <function>array_agg</function> ( <type>anyarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>anyarray</returnvalue>
</para>
<para>
@@ -20526,14 +20533,14 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>json_agg</primary>
</indexterm>
- <function>json_agg</function> ( <type>anyelement</type> )
+ <function>json_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
<indexterm>
<primary>jsonb_agg</primary>
</indexterm>
- <function>jsonb_agg</function> ( <type>anyelement</type> )
+ <function>jsonb_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
@@ -20573,7 +20580,8 @@ SELECT NULLIF(value, '(none)') ...
</indexterm>
<function>json_object_agg</function> ( <parameter>key</parameter>
<type>"any"</type>, <parameter>value</parameter>
- <type>"any"</type> )
+ <type>"any"</type>
+ <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
@@ -20582,7 +20590,8 @@ SELECT NULLIF(value, '(none)') ...
</indexterm>
<function>jsonb_object_agg</function> ( <parameter>key</parameter>
<type>"any"</type>, <parameter>value</parameter>
- <type>"any"</type> )
+ <type>"any"</type>
+ <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
@@ -20819,7 +20828,8 @@ SELECT NULLIF(value, '(none)') ...
</para>
<para role="func_signature">
<function>string_agg</function> ( <parameter>value</parameter>
- <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type> )
+ <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type>
+ <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>bytea</returnvalue>
</para>
<para>
@@ -20877,7 +20887,7 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>xmlagg</primary>
</indexterm>
- <function>xmlagg</function> ( <type>xml</type> )
+ <function>xmlagg</function> ( <type>xml</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
<returnvalue>xml</returnvalue>
</para>
<para>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 3ba844057f..ec089fac06 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1647,7 +1647,26 @@ sqrt(2)
are always just expressions and cannot be output-column names or numbers.
For example:
<programlisting>
-SELECT array_agg(a ORDER BY b DESC) FROM table;
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(v ORDER BY v DESC) FROM vals;
+ array_agg
+-------------
+ {4,3,3,2,1}
+</programlisting>
+ Since <type>jsonb</type> only keeps the last matching key, ordering
+ of its keys can be significant:
+<programlisting>
+WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
+SELECT jsonb_object_agg(k, v) FROM vals;
+ jsonb_object_agg
+----------------------------
+ {"key0": "1", "key1": "2"}
+
+WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
+SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
+ jsonb_object_agg
+----------------------------
+ {"key0": "1", "key1": "3"}
</programlisting>
</para>
@@ -1668,20 +1687,19 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
</para>
<para>
- If <literal>DISTINCT</literal> is specified in addition to an
- <replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal>
- expressions must match regular arguments of the aggregate; that is,
- you cannot sort on an expression that is not included in the
- <literal>DISTINCT</literal> list.
+ If <literal>DISTINCT</literal> is specified with an
+ <replaceable>order_by_clause</replaceable>, <literal>ORDER
+ BY</literal> expressions can only reference columns in the
+ <literal>DISTINCT</literal> list. For example:
+<programlisting>
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
+ array_agg
+-----------
+ {4,3,2,1}
+</programlisting>
</para>
- <note>
- <para>
- The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal>
- in an aggregate function is a <productname>PostgreSQL</productname> extension.
- </para>
- </note>
-
<para>
Placing <literal>ORDER BY</literal> within the aggregate's regular argument
list, as described so far, is used when ordering the input rows for
