Resending to correct a copy/paste error. Apologies.
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Yeah -- my argument would be that the = operator in NULLIF should be
> treated the same as if the function-like abbreviation were rewritten
> to the full CASE predicate. It doesn't surprise me that that is
> taken as text, given that they are both unadorned character string
> literals. The surprise here (for me at least) that the following
> generates a null of type text instead of matching the non-NULL input
> argument or (failing that) unknown, assuming the rewrite of
> NULLIF(a, b) to the equivalent CASE predicate:
>
> test=# select pg_typeof(case when null = 0 then null else null end);
> pg_typeof
> -----------
> text
> (1 row)
Symmetry fails here -- NULLIF is *not* treated the same as the CASE
predicate for which it is the abbreviation, which is arguably a
bug-level deviation from the SQL standard. Compare the above to:
test=# select pg_typeof(nullif(null, 0));pg_typeof
-----------integer
(1 row)
Which is the result I would want and expect, but is inconsistent with
treating it as an abbreviation of CASE.
-Kevin