add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tab1
("month1" timestamp, "month2" timestamp, "units" varchar(4))
;
INSERT INTO tab1
("month1", "month2", "units")
VALUES
('2012-01-01', NULL, '10'),
('2012-01-01', '2012-01-01', NULL),
('2012-01-01', '2012-03-01', '15'),
('2012-01-01', '2013-01-01', '12'),
('2012-01-01', '2013-04-01', '17'),
('2012-01-01', '2013-05-05', '5'),
('2020-01-01', '2020-01-01', '5')
;

CREATE TABLE

INSERT 0 7
SELECT s.date1
,COALESCE(t."month2",s.date2) as month2
,t."units"
FROM (
SELECT (t._year || '-01-01') ::date as date1, generate_series( (t._year || '-01-01') ::date
, (date_part('year', CURRENT_DATE) || '-12-01') ::date
, interval '1 month'
)::date AS date2
FROM (SELECT DISTINCT
extract(year from "month1") _year FROM tab1) t
) s
LEFT JOIN tab1 t
ON date_trunc('month',t."month1"::date)::date = s.date1
AND date_trunc('month',t."month2"::date)::date = s.date2
ORDER BY 1,2;
date1 month2 units
2012-01-01 2012-01-01 00:00:00 null
2012-01-01 2012-02-01 00:00:00 null
2012-01-01 2012-03-01 00:00:00 15
2012-01-01 2012-04-01 00:00:00 null
2012-01-01 2012-05-01 00:00:00 null
2012-01-01 2012-06-01 00:00:00 null
2012-01-01 2012-07-01 00:00:00 null
2012-01-01 2012-08-01 00:00:00 null
2012-01-01 2012-09-01 00:00:00 null
2012-01-01 2012-10-01 00:00:00 null
2012-01-01 2012-11-01 00:00:00 null
2012-01-01 2012-12-01 00:00:00 null
2012-01-01 2013-01-01 00:00:00 12
2012-01-01 2013-02-01 00:00:00 null
2012-01-01 2013-03-01 00:00:00 null
2012-01-01 2013-04-01 00:00:00 17
2012-01-01 2013-05-05 00:00:00 5
2012-01-01 2013-06-01 00:00:00 null
2012-01-01 2013-07-01 00:00:00 null
2012-01-01 2013-08-01 00:00:00 null
2012-01-01 2013-09-01 00:00:00 null
2012-01-01 2013-10-01 00:00:00 null
2012-01-01 2013-11-01 00:00:00 null
2012-01-01 2013-12-01 00:00:00 null
2012-01-01 2014-01-01 00:00:00 null
2012-01-01 2014-02-01 00:00:00 null
2012-01-01 2014-03-01 00:00:00 null
2012-01-01 2014-04-01 00:00:00 null
2012-01-01 2014-05-01 00:00:00 null
2012-01-01 2014-06-01 00:00:00 null
2012-01-01 2014-07-01 00:00:00 null
2012-01-01 2014-08-01 00:00:00 null
2012-01-01 2014-09-01 00:00:00 null
2012-01-01 2014-10-01 00:00:00 null
2012-01-01 2014-11-01 00:00:00 null
2012-01-01 2014-12-01 00:00:00 null
2012-01-01 2015-01-01 00:00:00 null
2012-01-01 2015-02-01 00:00:00 null
2012-01-01 2015-03-01 00:00:00 null
2012-01-01 2015-04-01 00:00:00 null
2012-01-01 2015-05-01 00:00:00 null
2012-01-01 2015-06-01 00:00:00 null
2012-01-01 2015-07-01 00:00:00 null
2012-01-01 2015-08-01 00:00:00 null
2012-01-01 2015-09-01 00:00:00 null
2012-01-01 2015-10-01 00:00:00 null
2012-01-01 2015-11-01 00:00:00 null
2012-01-01 2015-12-01 00:00:00 null
2012-01-01 2016-01-01 00:00:00 null
2012-01-01 2016-02-01 00:00:00 null
2012-01-01 2016-03-01 00:00:00 null
2012-01-01 2016-04-01 00:00:00 null
2012-01-01 2016-05-01 00:00:00 null
2012-01-01 2016-06-01 00:00:00 null
2012-01-01 2016-07-01 00:00:00 null
2012-01-01 2016-08-01 00:00:00 null
2012-01-01 2016-09-01 00:00:00 null
2012-01-01 2016-10-01 00:00:00 null
2012-01-01 2016-11-01 00:00:00 null
2012-01-01 2016-12-01 00:00:00 null
2012-01-01 2017-01-01 00:00:00 null
2012-01-01 2017-02-01 00:00:00 null
2012-01-01 2017-03-01 00:00:00 null
2012-01-01 2017-04-01 00:00:00 null
2012-01-01 2017-05-01 00:00:00 null
2012-01-01 2017-06-01 00:00:00 null
2012-01-01 2017-07-01 00:00:00 null
2012-01-01 2017-08-01 00:00:00 null
2012-01-01 2017-09-01 00:00:00 null
2012-01-01 2017-10-01 00:00:00 null
2012-01-01 2017-11-01 00:00:00 null
2012-01-01 2017-12-01 00:00:00 null
2012-01-01 2018-01-01 00:00:00 null
2012-01-01 2018-02-01 00:00:00 null
2012-01-01 2018-03-01 00:00:00 null
2012-01-01 2018-04-01 00:00:00 null
2012-01-01 2018-05-01 00:00:00 null
2012-01-01 2018-06-01 00:00:00 null
2012-01-01 2018-07-01 00:00:00 null
2012-01-01 2018-08-01 00:00:00 null
2012-01-01 2018-09-01 00:00:00 null
2012-01-01 2018-10-01 00:00:00 null
2012-01-01 2018-11-01 00:00:00 null
2012-01-01 2018-12-01 00:00:00 null
2012-01-01 2019-01-01 00:00:00 null
2012-01-01 2019-02-01 00:00:00 null
2012-01-01 2019-03-01 00:00:00 null
2012-01-01 2019-04-01 00:00:00 null
2012-01-01 2019-05-01 00:00:00 null
2012-01-01 2019-06-01 00:00:00 null
2012-01-01 2019-07-01 00:00:00 null
2012-01-01 2019-08-01 00:00:00 null
2012-01-01 2019-09-01 00:00:00 null
2012-01-01 2019-10-01 00:00:00 null
2012-01-01 2019-11-01 00:00:00 null
2012-01-01 2019-12-01 00:00:00 null
2012-01-01 2020-01-01 00:00:00 null
2012-01-01 2020-02-01 00:00:00 null
2012-01-01 2020-03-01 00:00:00 null
2012-01-01 2020-04-01 00:00:00 null
2012-01-01 2020-05-01 00:00:00 null
2012-01-01 2020-06-01 00:00:00 null
2012-01-01 2020-07-01 00:00:00 null
2012-01-01 2020-08-01 00:00:00 null
2012-01-01 2020-09-01 00:00:00 null
2012-01-01 2020-10-01 00:00:00 null
2012-01-01 2020-11-01 00:00:00 null
2012-01-01 2020-12-01 00:00:00 null
2012-01-01 2021-01-01 00:00:00 null
2012-01-01 2021-02-01 00:00:00 null
2012-01-01 2021-03-01 00:00:00 null
2012-01-01 2021-04-01 00:00:00 null
2012-01-01 2021-05-01 00:00:00 null
2012-01-01 2021-06-01 00:00:00 null
2012-01-01 2021-07-01 00:00:00 null
2012-01-01 2021-08-01 00:00:00 null
2012-01-01 2021-09-01 00:00:00 null
2012-01-01 2021-10-01 00:00:00 null
2012-01-01 2021-11-01 00:00:00 null
2012-01-01 2021-12-01 00:00:00 null
2012-01-01 2022-01-01 00:00:00 null
2012-01-01 2022-02-01 00:00:00 null
2012-01-01 2022-03-01 00:00:00 null
2012-01-01 2022-04-01 00:00:00 null
2012-01-01 2022-05-01 00:00:00 null
2012-01-01 2022-06-01 00:00:00 null
2012-01-01 2022-07-01 00:00:00 null
2012-01-01 2022-08-01 00:00:00 null
2012-01-01 2022-09-01 00:00:00 null
2012-01-01 2022-10-01 00:00:00 null
2012-01-01 2022-11-01 00:00:00 null
2012-01-01 2022-12-01 00:00:00 null
2020-01-01 2020-01-01 00:00:00 5
2020-01-01 2020-02-01 00:00:00 null
2020-01-01 2020-03-01 00:00:00 null
2020-01-01 2020-04-01 00:00:00 null
2020-01-01 2020-05-01 00:00:00 null
2020-01-01 2020-06-01 00:00:00 null
2020-01-01 2020-07-01 00:00:00 null
2020-01-01 2020-08-01 00:00:00 null
2020-01-01 2020-09-01 00:00:00 null
2020-01-01 2020-10-01 00:00:00 null
2020-01-01 2020-11-01 00:00:00 null
2020-01-01 2020-12-01 00:00:00 null
2020-01-01 2021-01-01 00:00:00 null
2020-01-01 2021-02-01 00:00:00 null
2020-01-01 2021-03-01 00:00:00 null
2020-01-01 2021-04-01 00:00:00 null
2020-01-01 2021-05-01 00:00:00 null
2020-01-01 2021-06-01 00:00:00 null
2020-01-01 2021-07-01 00:00:00 null
2020-01-01 2021-08-01 00:00:00 null
2020-01-01 2021-09-01 00:00:00 null
2020-01-01 2021-10-01 00:00:00 null
2020-01-01 2021-11-01 00:00:00 null
2020-01-01 2021-12-01 00:00:00 null
2020-01-01 2022-01-01 00:00:00 null
2020-01-01 2022-02-01 00:00:00 null
2020-01-01 2022-03-01 00:00:00 null
2020-01-01 2022-04-01 00:00:00 null
2020-01-01 2022-05-01 00:00:00 null
2020-01-01 2022-06-01 00:00:00 null
2020-01-01 2022-07-01 00:00:00 null
2020-01-01 2022-08-01 00:00:00 null
2020-01-01 2022-09-01 00:00:00 null
2020-01-01 2022-10-01 00:00:00 null
2020-01-01 2022-11-01 00:00:00 null
2020-01-01 2022-12-01 00:00:00 null 
SELECT 168