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.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