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.
>
> The COALESCE(<value expression>, ..., <value expression>) scans the list of
> <value expression>s from left to right, determines the highest data type in
What is a highest data type? The first data type?
> 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.
>
> 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?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +