On Sat, Sep 1, 2012 at 6:19 AM, David Johnston <polobo@xxxxxxxxx> wrote: > On Aug 31, 2012, at 22:49, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > >> David Johnston <polobo@xxxxxxxxx> writes: >>> On Aug 31, 2012, at 21:52, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >>>> David Johnston <polobo@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
