How to return 0 instead of NULL in postgresql?

Question

I'm having some trouble joining the contents of two tables. I want 0 instead of NULL in the result. Here's the current situation:

table1

Name    v1       v2
A       1         2
B       5         3
C       8         4

table2

Name    v3       v4    Id
B       8        12    1
B       7        22    3
C       6         4    2

Result

Name    v3       v4 
A       NULL     NULL
B       8        12
C       NULL     NULL

Expected Result

Name    v3       v4 
A        0        0
B        8       12
C        0        0

I've tried the following to achieve the result:

select t1.Name,
    (select coalesce(v3,0) from table2 where Name = t1.Name and id =1),    
    (select coalesce(v4,0) from table2 where Name= t1.Name and id =1)
from table1 t1

Schwern · Accepted Answer · 2019-01-24 07:03:26Z

4

You have to use coalesce outside subquery

select t1.Name,
       coalesce((select v3 from table2 where Name= t1.Name and id = 1), 0),
       coalesce((select v4 from table2 where Name= t1.Name and id = 1), 0)
  from table1 t1

or better, use left join instead correlated subqueries (correlated subqueries in value list should be terrible slow on larger than small tables).

select t1.name, coalesce(t2.v3, 0), coalesce(t2.v4, 0)
  from table1 t1 
       left join table2 t2 on t1.name = t2.name and t2.id = 1;

edited Jan 24, 2019 at 7:03

Schwern

167k28 gold badges225 silver badges370 bronze badges

answered Jan 24, 2019 at 6:55

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Schwern Over a year ago

To explain why, the subqueries run on each row of table1 and use the returned row to fill in the value. select ... from table2 where Name = t1.Name and id =1 returns no rows when t1.name is A or C, so the coalesce doesn't even get a chance to run.

Collectives™ on Stack Overflow

How to return 0 instead of NULL in postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related