| From: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
|---|---|
| To: | Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> |
| Cc: | David Johnston <polobo(at)yahoo(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: CASE/WHEN behavior with NULLS |
| Date: | 2012-09-02 11:41:37 |
| Message-ID: | 504345F1.2000006@ringerc.id.au |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On 09/01/2012 02:07 PM, Thalis Kalfigkopoulos wrote:
> 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.
The main thing to remember is that there are no consistent rules around
NULL. Learn each case and don't try to generalize too much.
Think:
1 + 2 + 3 = 6
1 + 2 + NULL = NULL
so obviously
sum(y) FROM ( VALUES (1),(2),(3) ) x(y) = 6
sum(y) FROM ( VALUES (1),(2),(NULL) ) x(y) = NULL
right? No, actually sum() over 1,2,NULL is 3, not NULL.
NULL isn't consistent.
--
Craig Ringer
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruce Momjian | 2012-09-02 12:12:33 | Re: [GENERAL] Multiple Slave Failover with PITR |
| Previous Message | Jasen Betts | 2012-09-02 07:54:08 | Re: GRANT SELECT |