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(dot)klaver(at)aklaver(dot)com