Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA87837 for ; Sun, 26 Sep 1999 20:05:59 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id UAA21874; Sun, 26 Sep 1999 20:04:53 -0400 (EDT) To: "Albert REINER" cc: PostgreSQL-SQL , Thomas Lockhart Subject: Re: [SQL] IFNULL - problem In-reply-to: Your message of Sun, 26 Sep 1999 17:28:37 +0200 <19990926172837.A261@frithjof> Date: Sun, 26 Sep 1999 20:04:53 -0400 Message-ID: <21872.938390693@sss.pgh.pa.us> From: Tom Lane "Albert REINER" 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 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 WHEN V1 IS NOT NULL THEN V1 ELSE V2 END 3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the following : 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: IFNULL(input,non-NULL substitute) doc/src/sgml/func.sgml: IFNULL(c1, 'N/A') 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