Add a missing monthly date in the time series data in PostgreSQL

id report_date price
1 2015-01-31 40
1 2015-02-28 56
1 2015-04-30 34
2 2014-05-31 45
2 2014-08-31 47

id report_date price
1 2015-01-31 40
1 2015-02-28 56
1 2015-03-31 0
1 2015-04-30 34
2 2014-05-31 45
2 2014-06-30 0
2 2014-07-31 0
2 2014-08-31 47 

with m as (
 select id, min(report_date) as minrd, max(report_date) as maxrd
 from t
 group by id
 )
select m.id, m.report_date, coalesce(t.price, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) as report_date
 from m< br />) m left join
 t
 on m.report_date = t.report_date;

with t as (
 select 1 as id, date '2012-01-31' as report_date, 10 as price union all
 select 1 as id, date '2012-04-30', 20
 ), m as (
 select id, min(report_date)-interval '1 day' as minrd, max(report_date)-interval '1 day' as maxrd
 from t
 group by id< br /> )
select m.id, m.report_date, coalesce(t.price, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) + interval '1 day' as report_date
 from m
) m left join
 t
 on m.report_date = t.report_date;

I have monthly time series data in the table, where the date is the last day of the month. Some dates are missing in the data. I want to insert these Date, and add zero values ​​for other attributes.
The table is as follows:

id report_date price
1 2015-01-31 40
1 2015-02-28 56
1 2015-04-30 34
2 2014-05-31 45
2 2014-08-31 47

I want Convert this table to

id report_date price
1 2015-01-31 40
1 2015-02-28 56
1 2015 -03-31 0
1 2015-04-30 34
2 2014-05-31 45
2 2014-06-30 0
2 2014-07-31 0< br />2 2014-08-31 47

Is there any way we can do this in Postgresql?
Currently we do this in Python. Because our data is growing day by day, and it is not efficient to process I/O for only one task.

Thank you

You can use generate_series() to generate dates, and then leave join to introduce values:

with m as (
 select id, min(report_date) as minrd, max(report_date) as maxrd
 from t
 group by id
 )
select m.id, m.report_date, coalesce(t. price, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) as report_date
 from m
) m left join
 t 
 on m.report_date = t.report_date;

Edit:

It turns out that the above situation is not completely valid, because adding the month at the end of the month does not keep the last day of the month .

This is easy to solve:

with t as (
 select 1 as id, date '2012-01-31' as report_date , 10 as price union all
 select 1 as id, date '2012-04-30', 20
 ), m as (
 select id, min(report_date)-interval '1 day 'as minrd, max(report_date)-interval '1 day' as maxrd
 from t
 group by id
 )
select m.id, m.report_date, coalesce(t .pri ce, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) + interval '1 day' as report_date
 from m
) m left join
 t
 on m.report_date = t.report_date;

The first CTE is just to generate sample data.