From: | Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | coalesce and aggregate functions |
Date: | 2006-12-12 15:22:19 |
Message-ID: | 20061212152219.GC290@quartz.itdept.newn.cam.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Is this a bug, or don't I understand coalesce()?
create table test (a int, b int);
insert into test values (1,null);
insert into test values (2,1);
insert into test values (2,2);
select * from test; -- returns:
select sum(b) from test where a=1; -- null
select sum(b) from test where a=2; -- 3
select coalesce(0,sum(b)) from test where a=1; -- 0
select coalesce(0,sum(b)) from test where a=2; -- 0
delete from test where a=1;
select coalesce(0,sum(b)) from test where a=2; -- 0 !
So when I use coalesce() with sum(), I always get the constant. I would
have expected it only in the case where sum() returns null..
What am I missing?
Cheers,
Patrick
(PostgreSQL 8.2devel of 21st November 2006)
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2006-12-12 15:33:04 | Re: coalesce and aggregate functions |
Previous Message | Kevin Grittner | 2006-12-12 15:03:23 | Re: Load distributed checkpoint |