Patrick Welche wrote:
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..
Coalesce returns the first non-null argument. In your example, 0 is
always the first non-null argument. You should be doing this instead:
select coalesce(sum(b),0) from test where a=2;
to get the desired effect.
BTW: This type of questions really belong to pgsql-general or
pgsql-novice, this list is for discussing development of PostgreSQL itself.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
