coalesce and aggregate functions - Mailing list pgsql-hackers

From Patrick Welche
Subject coalesce and aggregate functions
Date
Msg-id 20061212152219.GC290@quartz.itdept.newn.cam.ac.uk
Whole thread Raw
Responses Re: coalesce and aggregate functions
Re: coalesce and aggregate functions
Re: coalesce and aggregate functions
List 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)


pgsql-hackers by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Load distributed checkpoint
Next
From: Heikki Linnakangas
Date:
Subject: Re: coalesce and aggregate functions