On Wed, Oct 25, 2023 at 04:14:11PM -0700, David G. Johnston wrote:
> Yeah, we punt on the entire concept in the data type section:
>
> "Managing these errors and how they propagate through calculations is the
> subject of an entire branch of mathematics and computer science and will not
> be
> discussed here," ...
>
> Also, I'm now led to believe that the relevant IEEE 754 floating point
> addition
> is indeed commutative. Given that, I am inclined to simply not add the order
> by clause at all to those four functions. (actually, you already got rid of
> the
> avg()s but the sum()s are still present, so just those two).
Ah, yes, sum() removed. Updated patch attached.
--
Bruce Momjian <br...@momjian.us> 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..b64733d8aa 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>
@@ -1673,6 +1692,14 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
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.
+ For example:
+<programlisting>
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(DISTINCT v) FROM vals;
+ array_agg
+-----------
+ {1,2,3,4}
+</programlisting>
</para>
<note>
