Re: COALESCE requires NULL from scalar subquery has a type

Geoff Winkless <pgsqladmin@geoff.dj> writes:
> SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
> gives an error

> I'm guessing this is because Postgres can't deduce the type of the
> string column from the source when the result isn't returned. Oddly,
> it also seems to cope when I do:
> SELECT COALESCE((SELECT 'Yes'::varchar FROM gwtest WHERE id=4), 'No') AS valid

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.

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.

            regards, tom lane