Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd4fd571

Browse files
committed
Doc: clarify data type behavior of COALESCE and NULLIF.
After studying the code, NULLIF is a lot more subtle than you mighthave guessed.Discussion:https://postgr.es/m/160486028730.25500.15740897403028593550@wrigleys.postgresql.org
1 parent3855e5b commitd4fd571

File tree

2 files changed

+33
-6
lines changed

2 files changed

+33
-6
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13909,6 +13909,12 @@ SELECT COALESCE(description, short_description, '(none)') ...
1390913909
<varname>short_description</varname> if it is not null, otherwise <literal>(none)</literal>.
1391013910
</para>
1391113911

13912+
<para>
13913+
The arguments must all be convertible to a common data type, which
13914+
will be the type of the result (see
13915+
<xref linkend="typeconv-union-case"/> for details).
13916+
</para>
13917+
1391213918
<para>
1391313919
Like a <token>CASE</token> expression, <function>COALESCE</function> only
1391413920
evaluates the arguments that are needed to determine the result;
@@ -13939,13 +13945,30 @@ SELECT COALESCE(description, short_description, '(none)') ...
1393913945
<programlisting>
1394013946
SELECT NULLIF(value, '(none)') ...
1394113947
</programlisting>
13942-
</para>
13943-
<para>
1394413948
In this example, if <literal>value</literal> is <literal>(none)</literal>,
1394513949
null is returned, otherwise the value of <literal>value</literal>
1394613950
is returned.
1394713951
</para>
1394813952

13953+
<para>
13954+
The two arguments must be of comparable types.
13955+
To be specific, they are compared exactly as if you had
13956+
written <literal><replaceable>value1</replaceable>
13957+
= <replaceable>value2</replaceable></literal>, so there must be a
13958+
suitable <literal>=</literal> operator available.
13959+
</para>
13960+
13961+
<para>
13962+
The result has the same type as the first argument &mdash; but there is
13963+
a subtlety. What is actually returned is the first argument of the
13964+
implied <literal>=</literal> operator, and in some cases that will have
13965+
been promoted to match the second argument's type. For
13966+
example, <literal>NULLIF(1, 2.2)</literal> yields <type>numeric</type>,
13967+
because there is no <type>integer</type> <literal>=</literal>
13968+
<type>numeric</type> operator,
13969+
only <type>numeric</type> <literal>=</literal> <type>numeric</type>.
13970+
</para>
13971+
1394913972
</sect2>
1395013973

1395113974
<sect2 id="functions-greatest-least">

‎doc/src/sgml/typeconv.sgml

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -127,8 +127,10 @@ must appear in a single set of columns, the types of the results of each
127127
<command>SELECT</command> clause must be matched up and converted to a uniform set.
128128
Similarly, the result expressions of a <literal>CASE</literal> construct must be
129129
converted to a common type so that the <literal>CASE</literal> expression as a whole
130-
has a known output type. The same holds for <literal>ARRAY</literal> constructs,
131-
and for the <function>GREATEST</function> and <function>LEAST</function> functions.
130+
has a known output type. Some other constructs, such
131+
as <literal>ARRAY[]</literal> and the <function>GREATEST</function>
132+
and <function>LEAST</function> functions, likewise require determination of a
133+
common type for several subexpressions.
132134
</para>
133135
</listitem>
134136
</varlistentry>
@@ -1041,9 +1043,11 @@ SQL <literal>UNION</literal> constructs must match up possibly dissimilar
10411043
types to become a single result set. The resolution algorithm is
10421044
applied separately to each output column of a union query. The
10431045
<literal>INTERSECT</literal> and <literal>EXCEPT</literal> constructs resolve
1044-
dissimilar types in the same way as <literal>UNION</literal>. The
1046+
dissimilar types in the same way as <literal>UNION</literal>.
1047+
Some other constructs, including
10451048
<literal>CASE</literal>, <literal>ARRAY</literal>, <literal>VALUES</literal>,
1046-
<function>GREATEST</function> and <function>LEAST</function> constructs use the identical
1049+
and the <function>GREATEST</function> and <function>LEAST</function>
1050+
functions, use the identical
10471051
algorithm to match up their component expressions and select a result
10481052
data type.
10491053
</para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp