Insert zero value rows for missing column value Postgres

Question

I have this table (elev_table) :

 country      | cat |    elev
--------------+-----+----------
 USA          |   0 |    41.46
 USA          |   1 |   878.90
 USA          |   2 |    78.01    
 CAN          |   0 |    46.00
 CAN          |   1 |    78.92
 CAN          |   2 |    78.01
 CAN          |   4 |   667.77
 CAN          |   5 |    10.80

How can I create a query to return the same table, filled with missing cat values? The cat column has to values from (-1 to 5) but not every country value contains every cat value.

Essentially, for every country in my table I need rows with elevation values for every cat value of -1 to 5.....

For example I want the resulting table to look like:

 country      | cat |    elev
--------------+-----+----------
 USA          |  -1 |        0
 USA          |   0 |    41.46
 USA          |   1 |   878.90
 USA          |   2 |    78.01
 USA          |   3 |        0
 USA          |   4 |        0
 USA          |   5 |        0  
 CAN          |  -1 |        0 
 CAN          |   0 |    46.00
 CAN          |   1 |    78.92
 CAN          |   2 |    78.01
 CAN          |   3 |        0
 CAN          |   4 |   667.77
 CAN          |   5 |    10.80

I believe 0 would be a good representation, but maybe NULL would be better?

I have tried the following query, but this doesn't create missing row values for each country... I know I am missing something but can't seem to figure out what!!!

with cat_series as(
  select generate_series(-1,5) as fullcat
)

select fullcat, coalesce(sum(t.elev),0), t.country
from cat_series
  left join elev_table t on cat_series.fullcat=t.cat
group by fullcat, t.country
order by fullcat, t.country;

Still quite new to postgres, sql. Any help or pointers would be appreciated!

Gordon Linoff · Accepted Answer · 2016-08-07 22:41:24Z

1

You can use a cross join followed by a left join:

select c.country, g.cat, coalesce(sum(t.elev), 0)
from (select distinct country from elev_table) c cross join
     generate_series(-1, 5) as g(cat) left join
     elev_table et
     on et.country = c.country and et.cat = g.cat
group by c.country, g.cat;

I left the group by in the query, although based on the sample data, this is sufficient:

select c.country, g.cat, coalesce(t.elev, 0)
from (select distinct country from elev_table) c cross join
     generate_series(-1, 5) as g(cat) left join
     elev_table et
     on et.country = c.country and et.cat = g.cat;

You can remove the coalesce() if you prefer NULL to 0.

answered Aug 7, 2016 at 22:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

F.Ronald Over a year ago

Lets say there is an additional column in the elev_table. Wouldn't any where statements cause the missing rows not to be filled? for example: in the below table WHERE et.color is not null) country | cat | elev | color --------------+-----+-----------+------- USA | 0 | 41.46 | blue USA | 2 | 78.01 | CAN | 0 | 46.00 | blue CAN | 1 | 78.92 | red CAN | 2 | 78.01 | CAN | 4 | 667.77 | blue CAN | 5 | 10.80 |

Gordon Linoff Over a year ago

@F.Ronald . . . A WHERE clause would affect the results. If you have another question, perhaps you should ask it as a question.

Collectives™ on Stack Overflow

Insert zero value rows for missing column value Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related