Re: COALESCE doccumentation - Mailing list pgsql-docs
| From | Daniil Treshchin |
|---|---|
| Subject | Re: COALESCE doccumentation |
| Date | |
| Msg-id | 1767763017.3390110.1556587876525@mail.yahoo.com Whole thread Raw |
| In response to | COALESCE doccumentation (PG Doc comments form <noreply@postgresql.org>) |
| Responses |
Re: COALESCE doccumentation
|
| List | pgsql-docs |
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 in the 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', 3, '1d', 1); -- cast error
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); -- errorSELECT 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>s
2) COALESCE determines the highest data type in the list of <value expressions>s
3) COALESCE casting the first non-NULL to the highest data type
4) 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.johnston@gmail.com> wrote:
On Mon, Apr 29, 2019 at 4:13 PM Bruce Momjian <bruce@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.
pgsql-docs by date: