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:
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.