pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIFNULL - problem
4+ messages / 3 participants
[nested] [flat]
* IFNULL - problem
@ 1999-09-26 15:28 Albert REINER <areiner@tph.tuwien.ac.at>
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-27 00:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Albert REINER <areiner@tph.tuwien.ac.at>
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-27 15:31 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-28 02:42 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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