On 05/05/2016 01:14 PM, Michael Moore wrote:
> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
> gives
> ERROR: failed to find conversion function from unknown to text
> ********** Error **********
>
> ERROR: failed to find conversion function from unknown to text
> SQL state: XX000
>
> So, I understand the datatype of 'null' is 'unknown', but what does
> 'text' have to do with it?
Hmm:
hplc=> SELECT null AS dt, null AS i; dt | i
----+--- |
(1 row)
hplc=> select null::text as dt, null::text as i; dt | i
----+--- |
(1 row)
hplc=> SELECT COALESCE(dt, i) FROM (SELECT null::text AS dt,
null::text AS i) q; coalesce
----------
(1 row)
hplc=> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
ERROR: failed to find conversion function from unknown to text
So it is not the conversion from NULL to text per se, just when it is
done on the output of a derived table. I don't why that is, maybe
someone else can chime in.
>
> Mike
>
--
Adrian Klaver
adrian.klaver@aklaver.com