Thread: If test in sql??
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? Thanks Esa
"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,): > "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 > >
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.