If test in sql??

"Esa Pikkarainen" wrote:

This is maybe a novice question, but after all...
Say, I have a discussion forum application. There is an expiration
value for a whole board; then there are individual values for every
conference. If this individual value is NULL then the general value
should be used. How can I test this situation in a query (to
Postgresql):
I mean something like:

Select (IF conf.expire NOTNULL conf.expire ELSE gen_expire)
as expire, ...

Where is this in manuals if it is?

SELECT COALESCE(expire, gen_expire) AS expire FROM conf;

COALESCE takes the first non-null value from its list of parameters, like
this:

junk=# select * from junk where name is null;
id | name
----+------
3 |
(1 row)

junk=# select id, coalesce(name,'no-name') from junk;
id | case
----+---------
1 | Fred
2 | George
4 | sdf%df
3 | no-name
(4 rows)

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If anyone has material possessions and sees his
brother in need but has no pity on him, how can the
love of God be in him?"
I John 3:17

Thank you very much. Fine! Just two little questiton s more:

1) Can COALESCE take more than two alternatives?

2) Does it treat a zero length string as NULL or NOT NULL?

Esa Pikkarainen

Oliver Elphick wrote (24 Jan 01,):

On Thu, Jan 25, 2001 at 10:01:21AM +0200,
Esa Pikkarainen <epikkara@ktk.oulu.fi> wrote:

Thank you very much. Fine! Just two little questiton s more:

1) Can COALESCE take more than two alternatives?

2) Does it treat a zero length string as NULL or NOT NULL?

Wouldn't it have been faster to fire up psql and try a select to see what
the answer is than send a message to this list?

COALESCE definitely works with more than 2 values, as I use it that way.
'' should not be the same as a null, but I haven't actually tested this
to make sure it works that way.