Re: How to get CASE statement to recognize null ?

[David Gauthier <davegauthierpg@xxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

David Gauthier <davegauthierpg@xxxxxxxxx> 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