How to get CASE statement to recognize null ?

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: How to get CASE statement to recognize null ?
Date: 2021-03-10 19:41:59
Message-ID: CAMBRECCXKr4x8NOtCrumAet80_uDGq_PZ45vHf7Qa8YB7+ES5A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-03-10 19:46:53 Re: How to get CASE statement to recognize null ?
Previous Message Sandeep Saxena 2021-03-10 18:56:57 pgAgent for multiple databases in same instance