pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
IFNULL - problem
4+ messages / 3 participants
[nested] [flat]

* IFNULL - problem
@ 1999-09-26 15:28 Albert REINER <areiner@tph.tuwien.ac.at>
  1999-09-27 00:04 ` Re: [SQL] IFNULL - problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Albert REINER @ 1999-09-26 15:28 UTC (permalink / raw)
  To: pgsql-sql

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 return second 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>
---------------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] IFNULL - problem
  1999-09-26 15:28 IFNULL - problem Albert REINER <areiner@tph.tuwien.ac.at>
@ 1999-09-27 00:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-09-27 15:31   ` Re: [SQL] IFNULL - problem Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-09-28 02:42   ` Re: [SQL] IFNULL - problem Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 2 replies; 4+ messages in thread

From: Tom Lane @ 1999-09-27 00:04 UTC (permalink / raw)
  To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql; Thomas Lockhart <lockhart@alumni.caltech.edu>

"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-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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] IFNULL - problem
  1999-09-26 15:28 IFNULL - problem Albert REINER <areiner@tph.tuwien.ac.at>
  1999-09-27 00:04 ` Re: [SQL] IFNULL - problem Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-09-27 15:31   ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 4+ messages in thread

From: Thomas Lockhart @ 1999-09-27 15:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Albert REINER <areiner@tph.tuwien.ac.at>; pgsql-sql

> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] IFNULL - problem
  1999-09-26 15:28 IFNULL - problem Albert REINER <areiner@tph.tuwien.ac.at>
  1999-09-27 00:04 ` Re: [SQL] IFNULL - problem Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-09-28 02:42   ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 4+ messages in thread

From: Thomas Lockhart @ 1999-09-28 02:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Constantin Teodorescu <teo@flex.ro>; +Cc: Albert REINER <areiner@tph.tuwien.ac.at>; pgsql-sql

> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-09-28 02:42 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-26 15:28 IFNULL - problem Albert REINER <areiner@tph.tuwien.ac.at>
1999-09-27 00:04 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-27 15:31   ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-09-28 02:42   ` Thomas Lockhart <lockhart@alumni.caltech.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox