Re: weird error message

Love it! Thanks David.

On Fri, May 6, 2016 at 8:22 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, May 6, 2016 at 6:53 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>>
>>
>> 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.
>>
>
> ​The message would be more accurately written "failed to find implicit
> conversion function from unknown to text".
>
> ​The answer is partially given by #3 in the type conversion documentation:
>
> ​http://www.postgresql.org/docs/9.5/interactive/typeconv-union-case.html
> """
> 3. If all inputs are of type unknown, resolve as type text (the preferred
> type of the string category).
> ​"""
>
> However, the conversion from unknown to text can only happen implicitly if
> the unknown type has not been "locked" due to the value in question being
> passed up from a subquery. The distinction is one I personally call
> "untyped" versus "unknown (typed)". An "untyped" value has an unknown type
> but it can be implicitly cast to any other type. If it ends up being cast
> so that it is "unknown typed" further implicit casts are not allowed -
> there are no implicit casts in the system from "unknown typed".
>
> So dt and i become unknown typed and then passed through the case which
> chooses text (per #3) as the common type but fails when it goes looking for
> an implicit cast from unknown type to text. Removing the subquery the case
> succeeded because the nulls are simply untyped.
>
> I don't know that this is covered all that well in the documentation.
> I've been meaning to write a patch to add substantially what I've written
> above (I've made this similar response a number of times now) but haven't
> gotten around to it yet. It doesn't come up that often and when it does it
> is confusion but hardly problematic.
>
> David J.
>
>
>