pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednull values to be replaced by a default value
3+ messages / 3 participants
[nested] [flat]
* null values to be replaced by a default value
@ 1999-04-20 15:31 Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-20 17:20 ` Re: [SQL] null values to be replaced by a default value Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-04-20 15:31 UTC (permalink / raw)
To: pgsql-sql
Hi,
To return one value not null, i've created a function :
CREATE FUNCTION isnull (int4,int4 ) RETURNS int4 AS '
BEGIN
IF ($1 IS NULL) THEN
RETURN $2;
ELSE
RETURN $1;
END IF;
END;
' LANGUAGE 'plpgsql';
select isnull(my_int,0) as number, sample_id from my_table;
but I got
number |sample_id
------+---------
| 13
| 15
What should I do to have '0' in the column 'number' instead of NULL ?
Thanks,
nuch
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] null values to be replaced by a default value
1999-04-20 15:31 null values to be replaced by a default value Nuchanard Chiannilkulchai <nuch@valigene.com>
@ 1999-04-20 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-04-20 17:20 UTC (permalink / raw)
To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql
Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
> To return one value not null, i've created a function :
You can't really do that with functions right now --- any null
input to a function causes the result to be null. (There's
been discussion on the hackers list about improving this,
but it won't happen before 6.6 at the earliest.)
Fortunately, the standard COALESCE operator does what you want
already: COALESCE(a,b) produces the result you were looking for,
and you won't need to make a new copy for every data type either.
Basically, COALESCE takes any number of arguments and returns the
first one that's not NULL. It's a shorthand for a CASE expression,
(CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ...)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: [SQL] null values to be replaced by a default value
@ 1999-04-20 17:13 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 3+ messages in thread
From: Michael J Davis @ 1999-04-20 17:13 UTC (permalink / raw)
To: pgsql-sql
I would really like to be able to do the same thing!!! The following
function seems to work okay:
CREATE FUNCTION nz (int4) RETURNS int4 AS '
BEGIN
IF ($1 IS NULL) THEN
RETURN 0;
ELSE
RETURN $1;
END IF;
END;
' LANGUAGE 'plpgsql';
-----Original Message-----
From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
Sent: Tuesday, April 20, 1999 9:32 AM
To: pgsql-sql@postgreSQL.org
Subject: [SQL] null values to be replaced by a default value
Hi,
To return one value not null, i've created a function :
CREATE FUNCTION isnull (int4,int4 ) RETURNS int4 AS '
BEGIN
IF ($1 IS NULL) THEN
RETURN $2;
ELSE
RETURN $1;
END IF;
END;
' LANGUAGE 'plpgsql';
select isnull(my_int,0) as number, sample_id from my_table;
but I got
number |sample_id
------+---------
| 13
| 15
What should I do to have '0' in the column 'number' instead of NULL
?
Thanks,
nuch
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-04-20 17:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-20 15:31 null values to be replaced by a default value Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-20 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-20 17:13 RE: [SQL] null values to be replaced by a default value Michael J Davis <michael.j.davis@tvguide.com>
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