Re: 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(dot)Elphick(at)lfix(dot)co(dot)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