From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CASE/WHEN behavior with NULLS |
Date: | 2012-09-01 00:25:55 |
Message-ID: | 82B33D06-50D4-4EE4-B03D-48CA79919428@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> wrote:
> Hello all,
>
> I have a query that presents a sum() where in some records it's NULL
> because all members of the group are NULL.
> I decided I wanted to see a pretty 0 instead of NULL since it fits the
> logic of the app.
>
> This didn't work as expected (the NULL's persisted):
> ...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...
Guessing this form effectively evaluates to
WHEN sum(foo) = NULL instead of IS NULL and thus the wrong answer:
>
> Whereas changing it to:
> ...CASE WHEN sum(foo) IS NULL THEN 0 ELSE sum(foo) END...
> it works as expected, substituting the sum()'s that are NULL to zeros.
>
> Is that expected behavior? Do i misunderstand how CASE/WHEN works?
>
Yes.
That said you might want to try
SUM(COALESCE(foo, 0))
or
SUM(case when foo is null then 0 else foo end)
Your current attempt does not handle mixed NULL and NOT NULL the way most people would want it to (though maybe you do...)
> Running: PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC)
> 3.4.6, 32-bit
>
> TIA,
> Thalis K.
>
>
David J
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-09-01 01:52:19 | Re: CASE/WHEN behavior with NULLS |
Previous Message | Steve Atkins | 2012-08-31 23:31:09 | Re: "Too far out of the mainstream" |