Re: COALESCE and NULLIF semantics

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> OK. The only time it would be different from current behavior is when
> all parameters are of unknown type -- the result would be unknown
> rather than text:

The difficulty with that is that it implies eventually having to coerce
from unknown to something else, only at runtime instead of parse time.
There is not actually any such thing as a runtime coercion from unknown.
What there is is parse-time determination of the type of a literal
constant.

Now admittedly there's probably not any major technical obstacle to
making a runtime conversion happen --- it's merely delayed invocation of
the destination type's input function. But I find it really ugly from a
theoretical point of view. Doing calculations with "unknown" values
just seems wrong. As an example consider

INSERT INTO tab (date_column) VALUES(COALESCE('2009-09-09', 'boo'));

If we made it work like you suggest, the system would never notice
that 'boo' is not a legal value of type date. I don't find that
to be a good idea.

For NULLIF the concept fails entirely, because you *can not* compare two
values without having determined what data type you intend to treat them
as. Ex: is '007' different from '7'?

regards, tom lane