On 8 February 2016 at 14:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Yup. The output column type of the sub-SELECT is determined without > reference to its context, so there's nothing causing the unknown-type > literal to get assigned a definite type.
Mm. I can follow that, although it makes me unhappy that casting the literal to a known type fixes this, it seems unintuitive.
While explicit casting of literals can at times be annoying and seemingly unncessary I wouldn't call it unintuitive. And, the errors are usually sufficiently specific to know where one is required.
> There's been occasional discussion of changing that behavior, but it's > not real clear that it wouldn't create as many problems as it solves.
A more simple solution (to my problem, at least!) might be to stop COALESCE trying to coerce NULLs into a type at all. I don't see how that could ever cause any problems, since NULL is only ever discarded in this context.
I would understand it would be difficult if the coercion is taking place at a higher level, but I don't see how that can be the case, because the type it tries to coerce the NULL into is defined by the second argument (which must be COALESCE-specific behaviour, I would think).
This has little to do with COALESCE, nor NULL, specifically. I may be over generalizing a bit here but consider that the select-list of a query returns strongly typed data - of which "unknown" is one such type. In some cases, say "INSERT INTO SELECT FROM", the surrounding context (in this case the relation referred to by the INSERT) can impart type information thus informing the SELECT query of the type for any "untyped" literals it is faced with and thus allows it to implicitly cast the "untyped" literal to the imparted type prior to freezing. However, when the SELECT is part of a sub-query no such contextual information is passed down to it and any "untyped" literals are thus frozen as "unknown" and then passed back up to the parent query. Typically, you cannot count on PostgreSQL to cast "unknown" typed data to other types.