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