Re: CASE/WHEN behavior with NULLS

On Sat, Sep 1, 2012 at 6:19 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> On Aug 31, 2012, at 22:49, 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 21:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> David Johnston <polobo(at)yahoo(dot)com> writes:
>>>>> 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.
>>
>> But SUM() ignores input nulls, so I think they really are equivalent.
>> I agree that in a lot of other cases (for instance MAX), you'd have to
>> think harder about which behavior you wanted.
>>
>
> This I did not know/recall, was assuming nulls poisoned the result.
>
> David J.

Thanks all for the replies. Actually I had already tested that sum()
behaved correctly with respect to NULLs, meaning that it ignored them
(or treated them as 0, couldn't really tell). That's why I went ahead
sum()ing even though I knew NULLs would always be involved.
Unfortunately I didn't see what now seems obvious, that the comparison
would be =NULL and not IS NULL.

Works fine with coalesc(sum(foo),0).

regards,
Thalis K.