Re: COALESCE requires NULL from scalar subquery has a type

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.

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

Geoff