On Aug 31, 2012, at 21:52, Tom Lane <[email protected]> wrote: > David Johnston <[email protected]> writes: >> On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos <[email protected]> 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. -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
