Geoff Winkless <pgsqladmin(at)geoff(dot)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