Re: [SQL] IFNULL - problem

"Albert REINER" <areiner(at)tph(dot)tuwien(dot)ac(dot)at> writes:
> Hi, I'm using PostgreSQL 6.5.1, and when I try to use IFNULL (listed
> in the SQL Functions section of the docs in

The docs are in error: it's spelled NULLIF. In fact they also get
the definition wrong. The SQL92 spec says

1) NULLIF (V1, V2) is equivalent to the following <case specifica-
tion>:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

which is not what you were expecting ... and not terribly useful IMHO,
but that's how the spec defines it. You probably want to use COALESCE
instead:

2) COALESCE (V1, V2) is equivalent to the following <case specifi-
cation>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
following <case specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
END

Note to doc maintainers: glimpse shows these references to 'ifnull':

doc/src/sgml/admin.sgml: Include mention of CASE, COALESCE, and IFNULL.
doc/src/sgml/func.sgml: <entry> IFNULL(<replaceable class="parameter">input</replaceable>,<replaceable class="parameter">non-NULL substitute</replaceable>) </entry>
doc/src/sgml/func.sgml: <entry> IFNULL(<replaceable class="parameter">c1</replaceable>, 'N/A')</entry>
src/bin/pgaccess/lib/help/sqlfunc.hlp: " {} "IFNULL(input,non-NULL substitute)" {bold} "
src/bin/pgaccess/lib/help/sqlfunc.hlp: IFNULL(c1, 'N/A')

Both func.sgml and sqlfunc.hlp give incorrect definitions as well as
giving the wrong spelling.

regards, tom lane