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

Commit24b83a5

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 parent180cf87 commit24b83a5

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
@@ -17286,6 +17286,12 @@ SELECT COALESCE(description, short_description, '(none)') ...
1728617286
<varname>short_description</varname> if it is not null, otherwise <literal>(none)</literal>.
1728717287
</para>
1728817288

17289+
<para>
17290+
The arguments must all be convertible to a common data type, which
17291+
will be the type of the result (see
17292+
<xref linkend="typeconv-union-case"/> for details).
17293+
</para>
17294+
1728917295
<para>
1729017296
Like a <token>CASE</token> expression, <function>COALESCE</function> only
1729117297
evaluates the arguments that are needed to determine the result;
@@ -17316,13 +17322,30 @@ SELECT COALESCE(description, short_description, '(none)') ...
1731617322
<programlisting>
1731717323
SELECT NULLIF(value, '(none)') ...
1731817324
</programlisting>
17319-
</para>
17320-
<para>
1732117325
In this example, if <literal>value</literal> is <literal>(none)</literal>,
1732217326
null is returned, otherwise the value of <literal>value</literal>
1732317327
is returned.
1732417328
</para>
1732517329

17330+
<para>
17331+
The two arguments must be of comparable types.
17332+
To be specific, they are compared exactly as if you had
17333+
written <literal><replaceable>value1</replaceable>
17334+
= <replaceable>value2</replaceable></literal>, so there must be a
17335+
suitable <literal>=</literal> operator available.
17336+
</para>
17337+
17338+
<para>
17339+
The result has the same type as the first argument &mdash; but there is
17340+
a subtlety. What is actually returned is the first argument of the
17341+
implied <literal>=</literal> operator, and in some cases that will have
17342+
been promoted to match the second argument's type. For
17343+
example, <literal>NULLIF(1, 2.2)</literal> yields <type>numeric</type>,
17344+
because there is no <type>integer</type> <literal>=</literal>
17345+
<type>numeric</type> operator,
17346+
only <type>numeric</type> <literal>=</literal> <type>numeric</type>.
17347+
</para>
17348+
1732617349
</sect2>
1732717350

1732817351
<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>
@@ -1040,9 +1042,11 @@ SQL <literal>UNION</literal> constructs must match up possibly dissimilar
10401042
types to become a single result set. The resolution algorithm is
10411043
applied separately to each output column of a union query. The
10421044
<literal>INTERSECT</literal> and <literal>EXCEPT</literal> constructs resolve
1043-
dissimilar types in the same way as <literal>UNION</literal>. The
1045+
dissimilar types in the same way as <literal>UNION</literal>.
1046+
Some other constructs, including
10441047
<literal>CASE</literal>, <literal>ARRAY</literal>, <literal>VALUES</literal>,
1045-
<function>GREATEST</function> and <function>LEAST</function> constructs use the identical
1048+
and the <function>GREATEST</function> and <function>LEAST</function>
1049+
functions, use the identical
10461050
algorithm to match up their component expressions and select a result
10471051
data type.
10481052
</para>

0 commit comments

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