postgres.git / summary / log / commit / refs

commit    24b83a5082541bdb1b333b7fcbe92f194128595c
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Mon Nov 09 17:02:24 2020 +0000

    Doc: clarify data type behavior of COALESCE and NULLIF.
    
    After studying the code, NULLIF is a lot more subtle than you might
    have guessed.
    
    Discussion: https://postgr.es/m/160486028730.25500.15740897403028593550@wrigleys.postgresql.org


 doc/src/sgml/func.sgml     | 27 +++++++++++++++++++++++++--
 doc/src/sgml/typeconv.sgml | 12 ++++++++----
 2 files changed, 33 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7b1dc264f68..2783985b552 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17286,6 +17286,12 @@ SELECT COALESCE(description, short_description, '(none)') ...
    <varname>short_description</varname> if it is not null, otherwise <literal>(none)</literal>.
   </para>
 
+   <para>
+    The arguments must all be convertible to a common data type, which
+    will be the type of the result (see
+    <xref linkend="typeconv-union-case"/> for details).
+   </para>
+
    <para>
     Like a <token>CASE</token> expression, <function>COALESCE</function> only
     evaluates the arguments that are needed to determine the result;
@@ -17316,13 +17322,30 @@ SELECT COALESCE(description, short_description, '(none)') ...
 <programlisting>
 SELECT NULLIF(value, '(none)') ...
 </programlisting>
-  </para>
-  <para>
    In this example, if <literal>value</literal> is <literal>(none)</literal>,
    null is returned, otherwise the value of <literal>value</literal>
    is returned.
   </para>
 
+  <para>
+   The two arguments must be of comparable types.
+   To be specific, they are compared exactly as if you had
+   written <literal><replaceable>value1</replaceable>
+   = <replaceable>value2</replaceable></literal>, so there must be a
+   suitable <literal>=</literal> operator available.
+  </para>
+
+  <para>
+   The result has the same type as the first argument &mdash; but there is
+   a subtlety.  What is actually returned is the first argument of the
+   implied <literal>=</literal> operator, and in some cases that will have
+   been promoted to match the second argument's type.  For
+   example, <literal>NULLIF(1, 2.2)</literal> yields <type>numeric</type>,
+   because there is no <type>integer</type> <literal>=</literal>
+   <type>numeric</type> operator,
+   only <type>numeric</type> <literal>=</literal> <type>numeric</type>.
+  </para>
+
   </sect2>
 
   <sect2 id="functions-greatest-least">
diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml
index cfeb851a507..810b0cb5573 100644
--- a/doc/src/sgml/typeconv.sgml
+++ b/doc/src/sgml/typeconv.sgml
@@ -127,8 +127,10 @@ must appear in a single set of columns, the types of the results of each
 <command>SELECT</command> clause must be matched up and converted to a uniform set.
 Similarly, the result expressions of a <literal>CASE</literal> construct must be
 converted to a common type so that the <literal>CASE</literal> expression as a whole
-has a known output type.  The same holds for <literal>ARRAY</literal> constructs,
-and for the <function>GREATEST</function> and <function>LEAST</function> functions.
+has a known output type.  Some other constructs, such
+as <literal>ARRAY[]</literal> and the <function>GREATEST</function>
+and <function>LEAST</function> functions, likewise require determination of a
+common type for several subexpressions.
 </para>
 </listitem>
 </varlistentry>
@@ -1040,9 +1042,11 @@ SQL <literal>UNION</literal> constructs must match up possibly dissimilar
 types to become a single result set.  The resolution algorithm is
 applied separately to each output column of a union query.  The
 <literal>INTERSECT</literal> and <literal>EXCEPT</literal> constructs resolve
-dissimilar types in the same way as <literal>UNION</literal>.  The
+dissimilar types in the same way as <literal>UNION</literal>.
+Some other constructs, including
 <literal>CASE</literal>, <literal>ARRAY</literal>, <literal>VALUES</literal>,
-<function>GREATEST</function> and <function>LEAST</function> constructs use the identical
+and the <function>GREATEST</function> and <function>LEAST</function>
+functions, use the identical
 algorithm to match up their component expressions and select a result
 data type.
 </para>


[parent: 180cf876d457]