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 mytable(
FROMDATE DATE NOT NULL
,TODATE DATE
);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2016','30/04/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/05/2016',NULL);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('2/06/2016','16/10/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('17/10/2016','31/12/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2017','14/02/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2017','31/12/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2018','14/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2018','20/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('21/02/2018','31/03/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2018','31/12/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2019','31/03/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2019','31/12/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2020',NULL);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select
fromdate
, todate as todate_raw
, coalesce(todate,lag(fromdate,1) over(order by fromdate DESC)) as todate_new
from mytable
order by fromdate
| fromdate | todate_raw | todate_new |
|---|---|---|
| 2016-02-15 | 2016-04-30 | 2016-04-30 |
| 2016-05-01 | null | 2016-06-02 |
| 2016-06-02 | 2016-10-16 | 2016-10-16 |
| 2016-10-17 | 2016-12-31 | 2016-12-31 |
| 2017-01-01 | 2017-02-14 | 2017-02-14 |
| 2017-02-15 | 2017-12-31 | 2017-12-31 |
| 2018-01-01 | 2018-02-14 | 2018-02-14 |
| 2018-02-15 | 2018-02-20 | 2018-02-20 |
| 2018-02-21 | 2018-03-31 | 2018-03-31 |
| 2018-04-01 | 2018-12-31 | 2018-12-31 |
| 2019-01-01 | 2019-03-31 | 2019-03-31 |
| 2019-04-01 | 2019-12-31 | 2019-12-31 |
| 2020-01-01 | null | null |