Re: COALESCE requires NULL from scalar subquery has a type

On 8 February 2016 at 16:05, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> While explicit casting of literals can at times be annoying and seemingly
> unncessary I wouldn't call it unintuitive.

Well.... that very much depends on your definition of intuitive. If
something is "seemingly unnecessary" I would say that's the same thing
as "unintuitive", isn't it?

> Typically, you cannot count on PostgreSQL to cast
> "unknown" typed data to other types.

I don't believe that I'm suggesting that Postgres should. As far as I
can see, COALESCE takes values of type anyelement and attempts to
decide if the types are the same: for example it's unexpectedly quite
happy to take

SELECT COALESCE('1', 0);

because (I guess) it takes the "unknown" typed literal '1' and decides
that it can coerce it into an int; note that it _won't_ do
COALESCE('1'::text, 0) because that is explicitly typed...

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.

Geoff