Hi Bruce, David.
Comment: "Without further elaboration as to the shortcomings"
I provided a clear description on why, but for some reason it was missed in
this thread.
Here it is:
The COALESCE(<value expression>, ..., <value expression>) scans the list of
<value expression>s from left to right, determines the highest data type inthe
list and returns the first non-NULL in the list, casting it to the highest data
type selected in the previous step, but if all the <value expressions>s are
NULL, the result is NULL. In case when any of the <value expressions>s in the
list cannot be evaluated to the highest data type then exception is thrown.
Question: What is a highest data type? The first data type?
This is not the first datatype but there is an order, for example:
numeric
integer
character varying
text
If you mix numeric, integers and text in any order, then it will be evaluated
to numeric;If you mix integers and text, then it will be evaluated to integer;
Again, from my example:
SELECT COALESCE(NULL, '12', 3, 1, 1); -- OK! evaluated to integer
SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK! evaluated to numeric
SELECT COALESCE(NULL, '12', 3, 1.2, 1); -- OK! evaluated to numeric
SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error
SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error
The documentation says nothing about it and the description is obscure.What the
docs are not saying:
1) COALESCE scans the list of <value expression>s2) COALESCE determines the
highest data type in the list of <value expressions>s3) COALESCE casting the
first non-NULL to the highest data type4) COALESCE evaluates every <value
expression>s to the highest datatype selected in step (2)
In addition the doc says:"Like a CASE expression, COALESCE only evaluates the
arguments that are needed to determine the result; that is, arguments to the
right of the first non-null argument are not evaluated."
This is so not true according to the doc and just breaks a simple test.
SELECT COALESCE(NULL, '12', '3', '1.2', (SELECT profile_code FROM profile LIMIT
1)); -- OK! evaluates to character varying
SELECT COALESCE(NULL, '12', 3, '1.2', (SELECT profile_code FROM profile LIMIT
1)); -- ERROR! failed to evaluate to the highest datatype - integer
Thank you,
On Monday, April 29, 2019, 4:46:39 PM PDT, David G. Johnston
<david.g.johns...@gmail.com> wrote:
On Mon, Apr 29, 2019 at 4:13 PM Bruce Momjian <br...@momjian.us> wrote:
On Sat, Apr 27, 2019 at 08:26:57PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/11/functions-conditional.html
> Description:
>
> The documentation says:
>
> "The COALESCE function returns the first of its arguments that is not null.
> Null is returned only if all arguments are null. It is often used to
> substitute a default value for null values when data is retrieved for
> display, for example"
>
> Which is NOT TRUE. In addition, it is obscure and misleading.
>
> Try, for example.
>
> SELECT COALESCE(NULL, '12', 3, 1, 1); -- OK!
> SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK!
> SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error
> SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error
What is wrong about the existing description? That it doesn't mention
mixing data types?
The OP's example demonstrates a more basic confusion about types that needs to
be learned independent of COALESCE.
The literals surrounded with single quotes have an unknown type as written.
Since other literals are plain numbers those are typed as integers. Since all
arguments to COALESCE need to be the same type (same applies to other functions
or things like UNION) the untyped literal is coerced to the known typed
arguments - which fails when the untyped literals are not pure numbers.
There is a concept of parent types - though I cannot find the rules for them at
the moment - but basically most things devolve toward text; the issue here is
there isn't any "devolving" (parent) involved - its a straight assignment of
the known integer type to the untyped literals.
Without further elaboration as to the shortcomings of the current documentation
its hard to add more material that isn't this section of the documentation's
place to teach.
David J.
