Postgres Pro
Downloads
Home   >   mailing lists

Thread: IFNULL - problem

IFNULL - problem

From
"Albert REINER"
Date:
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
/doc.html/user/functions.htm#AEN2121, as:
  Table 5-1. SQL Functions  Function Returns Description Example  ...  IFNULL(input,non-NULL substitute) non-NULL
returnsecond argument if  first is NULL IFNULL(c1, 'N/A')  ...
 

), psql complains about not knowing such a function:

albert=> select ifnull(NULL, 'ASDF');
ERROR:  No such function 'ifnull' with the specified attributes
albert=> select ifnull(1, 2);
ERROR:  No such function 'ifnull' with the specified attributes

What I really want to do is something like:

albert=> \d mailcalendar
Table    = mailcalendar
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| base                             | datetime                         |     8 |
| repeat                           | reltime                          |     4 |
| offs                             | reltime                          |     4 |
| next                             | datetime                         |     8 |
| mail                             | text                             |   var |
+----------------------------------+----------------------------------+-------+
albert=> select ifnull(next, base) as asdf from mailcalendar;
ERROR:  No such function 'ifnull' with the specified attributes

What's wrong here? Am I just misusing this feature, or is it really
missing? I know that I can work around it by using case, but this is a
bit less convenient.

albert=> select case when next is null then base else next end as asdf from mailcalendar;
asdf
----------------------------
Sun Sep 26 00:00:00 1971 MET
(1 row)


Albert.

-- 

--------------------------------------------------------------------------- Post an / Mail to / Skribu al: Albert
Reiner<areiner@tph.tuwien.ac.at>
 
---------------------------------------------------------------------------

Re: [SQL] IFNULL - problem

From
Tom Lane
Date:
"Albert REINER" <areiner@tph.tuwien.ac.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-NULLsubstitute</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

Re: [SQL] IFNULL - problem

From
Thomas Lockhart
Date:
> Both func.sgml and sqlfunc.hlp give incorrect definitions as well as
> giving the wrong spelling.

Thanks for the hints. Will look at the docs.
               - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Re: [SQL] IFNULL - problem

From
Thomas Lockhart
Date:
> The docs are in error: it's spelled NULLIF.  In fact they also get
> the definition wrong.  The SQL92 spec says...
> Both func.sgml and sqlfunc.hlp give incorrect definitions as well as
> giving the wrong spelling.

I've got the sgml docs fixed locally, and will commit sometime soon
(the next few days). The other place to fix is in the pgaccess docs,
which I suppose are maintained elsewhere. Constantin?
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California