Re: weird error message

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