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 tbl (
id int
, value text
, start_date date
, end_date date
);
INSERT INTO tbl VALUES
(1, 'ABC', '2024-01-29', '2024-02-01')
, (2, 'XYZ', '2024-02-28', '2024-03-01')
;
CREATE TABLE
INSERT 0 2
-- SRF in SELECT list
SELECT t.id, t.value
, generate_series(t.start_date::timestamp
, t.end_date::timestamp
, interval '1 day')::date AS split_date
FROM tbl t;
| id | value | split_date |
|---|---|---|
| 1 | ABC | 2024-01-29 |
| 1 | ABC | 2024-01-30 |
| 1 | ABC | 2024-01-31 |
| 1 | ABC | 2024-02-01 |
| 2 | XYZ | 2024-02-28 |
| 2 | XYZ | 2024-02-29 |
| 2 | XYZ | 2024-03-01 |
SELECT 7
-- More verbose "standard-SQL" way with LATERAL subquery
SELECT t.id, t.value, split_date::date
FROM tbl t, generate_series(t.start_date::timestamp
, t.end_date::timestamp
, interval '1 day') split_date
| id | value | split_date |
|---|---|---|
| 1 | ABC | 2024-01-29 |
| 1 | ABC | 2024-01-30 |
| 1 | ABC | 2024-01-31 |
| 1 | ABC | 2024-02-01 |
| 2 | XYZ | 2024-02-28 |
| 2 | XYZ | 2024-02-29 |
| 2 | XYZ | 2024-03-01 |
SELECT 7