How to get CASE statement to recognize null ?
This is probably an easy one for someone with experience using CASE, but
intuitively I can't get it.
First... 11.5 on linux.
Example...
dvdb=# create table foo (col1 varchar, col2 varchar);
CREATE TABLE
dvdb=# insert into foo (col1,col2) values ('a','x'), (null,'y');
INSERT 0 2
dvdb=# select * from foo;
col1 | col2
------+------
a | x
| y
(2 rows)
dvdb=# select
CASE col1
WHEN null THEN 'z'
ELSE col1
END as col1,
col2
from foo;
col1 | col2
------+------
a | x
| y
(2 rows)
For the 2nd rec, col1 is null, so why wasn't it changed to 'z' ?
dvdb=# select
CASE col1
WHEN null THEN col1
ELSE 'z'
END as col1,
col2
from foo;
col1 | col2
------+------
z | x
z | y
(2 rows)
The 'a' in col1 of the first rec got clobbered as if it matched "null".
Obviously the problem has to do with how I'm specifying "null". But what's
the right way to do that ?
Thanks in Advance
David Gauthier <davegauthierpg@gmail.com> writes:
dvdb=# select
CASE col1
WHEN null THEN 'z'
ELSE col1
END as col1,
col2
from foo;
This test is equivalent to "col1 = null" which will always fail.
You could try something like
CASE WHEN col1 IS NULL THEN ... ELSE ... END
Although I think the particular thing you're doing here would
be better solved with COALESCE(col1, 'z').
regards, tom lane
Hey, Thanks Tom !
It's actually a little more complicated than the simple example, so I'm not
sure it can be shoehorned into coalesce...
CASE
WHEN sr.nightly_cl_display_suffix is null THEN cast (d.p4_changelist
as varchar)
ELSE
cast (d.p4_changelist as varchar)||'-'||sr.nightly_cl_display_suffix
END as changelist
On Wed, Mar 10, 2021 at 2:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
David Gauthier <davegauthierpg@gmail.com> writes:
dvdb=# select
CASE col1
WHEN null THEN 'z'
ELSE col1
END as col1,
col2
from foo;This test is equivalent to "col1 = null" which will always fail.
You could try something likeCASE WHEN col1 IS NULL THEN ... ELSE ... END
Although I think the particular thing you're doing here would
be better solved with COALESCE(col1, 'z').regards, tom lane
On 10 Mar 2021, at 21:00, David Gauthier <davegauthierpg@gmail.com> wrote:
Hey, Thanks Tom !
It's actually a little more complicated than the simple example, so I'm not sure it can be shoehorned into coalesce...
CASE
WHEN sr.nightly_cl_display_suffix is null THEN cast (d.p4_changelist as varchar)
ELSE
cast (d.p4_changelist as varchar)||'-'||sr.nightly_cl_display_suffix
END as changelist
Sure it can:
cast(d.p4_changelist as varchar) || coalesce('-'||sr.nightly_cl_display_suffix, '')
On Wed, Mar 10, 2021 at 2:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Gauthier <davegauthierpg@gmail.com> writes:dvdb=# select
CASE col1
WHEN null THEN 'z'
ELSE col1
END as col1,
col2
from foo;This test is equivalent to "col1 = null" which will always fail.
You could try something likeCASE WHEN col1 IS NULL THEN ... ELSE ... END
Although I think the particular thing you're doing here would
be better solved with COALESCE(col1, 'z').regards, tom lane
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.