Re: CASE/WHEN behavior with NULLS

On Aug 31, 2012, at 21:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Johnston <polobo(at)yahoo(dot)com> writes:
>> On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> wrote:
>>> 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:
>
> Yeah, I think that's right.
>
>> That said you might want to try
>> SUM(COALESCE(foo, 0))
>
> Actually I'd go with "COALESCE(SUM(foo), 0)" since that requires only
> one COALESCE operation, not one per row.
>
>

These are not equivalent if some values of foo are not-null and you want the sum of all non-null values while replacing any nulls with zero. So the decision depends on what and why you are summing.

As an alternative for the original question the coalesce(sum(foo),0) form is indeed better.

David J.