From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | David Gauthier <davegauthierpg(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to get CASE statement to recognize null ? |
Date: | 2021-03-10 20:17:46 |
Message-ID: | F24437A8-23F9-4DAC-86F6-84A4F1045F5C@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 10 Mar 2021, at 21:00, David Gauthier <davegauthierpg(at)gmail(dot)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(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
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | Stacey Haysler | 2021-03-11 03:48:58 | Code of Conduct Russian Translation - Revised March 5, 2021 |
Previous Message | Adam Brusselback | 2021-03-10 20:16:11 | Re: pgAgent for multiple databases in same instance |