Re: COALESCE requires NULL from scalar subquery has a type

On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> On 8 February 2016 at 14:49, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

David J.