Re: How to get CASE statement to recognize null ?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Gauthier <davegauthierpg(at)gmail(dot)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
>