Re: coalesce and aggregate functions

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