Re: weird error message

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.