Thread: Where is IFNULL?

Hi,

the user manual describes the function IFNULL on page 38, but when I
try to use it I get the following error:

  users=> select ifnull (NULL, 'nix');
  ERROR:  No such function 'ifnull' with the specified attributes

What do I have to do to get the function?

Or does anybody know a simple way to concatenate strings, which can be
NULL?

Sascha

> Hi,
>
> the user manual describes the function IFNULL on page 38, but when I
> try to use it I get the following error:
>
>   users=> select ifnull (NULL, 'nix');
>   ERROR:  No such function 'ifnull' with the specified attributes

The manual seems to be obsolete. The related functions are named
nullvalue and nonnullvalue, but neither takes more than one argument.

Actually, there is a SQL syntactic sugar for null/not null:

diagrams=> select 1 is not null;
?column?
--------
t
(1 row)

diagrams=> select 1 is null;
?column?
--------
f
(1 row)

'1 is null' is equivalent to 'nullvalue(1)'


> Or does anybody know a simple way to concatenate strings, which can be
> NULL?

Don't know the answer to this, but it appears to be wrong not to
ignore NULLs in concatenation. Why not trust this job to your client-side
code?

--Gene

"Gene Selkov Jr." <selkovjr@selkovjr.xnet.com>:

| > the user manual describes the function IFNULL on page 38, but when I
| > try to use it I get the following error:
| >
| >   users=> select ifnull (NULL, 'nix');
| >   ERROR:  No such function 'ifnull' with the specified attributes
|
| The manual seems to be obsolete. The related functions are named
| nullvalue and nonnullvalue, but neither takes more than one argument.

IFNULL isn't syntactic suguar.  It is like C's "1 ? 1 : 0".

| > Or does anybody know a simple way to concatenate strings, which can be
| > NULL?
|
| Don't know the answer to this, but it appears to be wrong not to
| ignore NULLs in concatenation.

That is the way Postgresql works:

users=> select 'tach' || NULL;
ERROR:  parser: parse error at or near ";"

users=> create function cat (text,text) returns text as 'begin return $1 || $2; end;' language 'plpgsql';
CREATE
users=> select cat ('tach', NULL);
ERROR:  typeidTypeRelid: Invalid type - oid = 0

| Why not trust this job to your client-side
| code?

Because I do not write that code and because my computer isn't
religious enough to trust anybody ;-)

Sascha

SELECT CASE WHEN b IS NOT NULL
THEN 'pref.'||b||'.suf'
ELSE 'pref.'||'NULL'||'.suf'
END FROM a;

case
--------------
pref.primo.suf
pref.NULL.suf
(2 rows)
 

Jose'

Sascha Ziemann wrote:

"Gene Selkov Jr." <selkovjr@selkovjr.xnet.com>:

| > the user manual describes the function IFNULL on page 38, but when I
| > try to use it I get the following error:
| >
| >   users=> select ifnull (NULL, 'nix');
| >   ERROR:  No such function 'ifnull' with the specified attributes
|
| The manual seems to be obsolete. The related functions are named
| nullvalue and nonnullvalue, but neither takes more than one argument.

IFNULL isn't syntactic suguar.  It is like C's "1 ? 1 : 0".

| > Or does anybody know a simple way to concatenate strings, which can be
| > NULL?
|
| Don't know the answer to this, but it appears to be wrong not to
| ignore NULLs in concatenation.

That is the way Postgresql works:

users=> select 'tach' || NULL;
ERROR:  parser: parse error at or near ";"

users=> create function cat (text,text) returns text as 'begin return $1 || $2; end;' language 'plpgsql';
CREATE
users=> select cat ('tach', NULL);
ERROR:  typeidTypeRelid: Invalid type - oid = 0

| Why not trust this job to your client-side
| code?

Because I do not write that code and because my computer isn't
religious enough to trust anybody ;-)

Sascha

************