Re: CASE/WHEN behavior with NULLS

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.