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