Re: COALESCE requires NULL from scalar subquery has a type

Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.

Leaving aside the question of whether that is actually feasible or
a good idea: how would that improve your original complaint?

SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;

There's no null visible anywhere in that. I suppose that if there's
no row with id=4, there would be a null at runtime, but that's not
going to make any difference for parse-time determination of what
type the COALESCE() will return.

regards, tom lane