2

I have some Postgres data like this:

date       | count
2015-01-01 | 20
2015-01-02 | 15
2015-01-05 | 30

I want to run a query that pulls this data with 0s in place for the dates that are missing, like this:

date       | count
2015-01-01 | 20
2015-01-02 | 15
2015-01-03 |  0
2015-01-04 |  0
2015-01-05 | 30

This is for a very large range of dates, and I need it to fill in all the gaps. How can I accomplish this with just SQL?

kid_drew
  • 3,857
  • 6
  • 28
  • 38

2 Answers2

2

Given a table junk of:

     d      | c  
------------+----
 2015-01-01 | 20
 2015-01-02 | 15
 2015-01-05 | 30

Running

select fake.d, coalesce(j.c, 0) as c
from (select min(d) + generate_series(0,7,1) as d from junk) fake
     left outer join junk j on fake.d=j.d;

gets us:

     d      |     c 
------------+----------
 2015-01-01 |       20
 2015-01-02 |       15
 2015-01-03 |        0
 2015-01-04 |        0
 2015-01-05 |       30
 2015-01-06 |        0
 2015-01-07 |        0
 2015-01-08 |        0

You could of course adjust the start date for the series, length it runs for, etc.

Jay Kominek
  • 8,674
  • 1
  • 34
  • 51
0

Where is this data going? To an outside source or another table or view?

There's probably a better solution but you could create a new table(or in excel wherever the data is going) that has the entire date-range you want with another integer column of null values. Then update that table with your current dataset then replace all nulls with zero.

It's a really roundabout way to do things but it'll work.

I don't have enough rep to comment :(

This is also a good reference Using COALESCE to handle NULL values in PostgreSQL

Community
  • 1
  • 1
mgtemp
  • 248
  • 3
  • 11
  • I don't see why it makes a difference. It's going to plug into a report framework that requires pure SQL, so I can't manipulate it with code at all. Note that questions should be entered as comments to my post, not as answers. – kid_drew Jun 05 '15 at 18:19