Subject: Re: weird error message
To: Michael Moore <michaeljmoore@gmail.com>,
 postgres list <pgsql-sql@postgresql.org>
References: 
 <CACpWLjMK8J2O_zjrhBgfYoYU=eO=+CNO7DE0BNM6YNTkbYV4Hw@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <e9b9f162-ad10-74a8-9f5a-3198b2a36144@aklaver.com>
Date: Fri, 6 May 2016 06:53:23 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:45.0) Gecko/20100101
 Thunderbird/45.0
MIME-Version: 1.0
In-Reply-To: 
 <CACpWLjMK8J2O_zjrhBgfYoYU=eO=+CNO7DE0BNM6YNTkbYV4Hw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql