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 vouchers (
id SERIAL PRIMARY KEY,
collected_date DATE,
collected_volume INT
);
INSERT INTO vouchers
(collected_date, collected_volume
)
VALUES
('2024-03-09', '900'),
('2024-04-20', '300'),
('2024-04-20', '800'),
('2024-05-24', '400'),
('2025-01-17', '200'),
('2025-02-15', '800'),
('2025-02-15', '150');
CREATE TABLE
INSERT 0 7
select
collected_date as collected_date,
(collected_date + interval '12 months')::date as expire_date,
sum(collected_volume) as collected_volume
from vouchers
group by 1,2
order by 1,2;
| collected_date | expire_date | collected_volume |
|---|---|---|
| 2024-03-09 | 2025-03-09 | 900 |
| 2024-04-20 | 2025-04-20 | 1100 |
| 2024-05-24 | 2025-05-24 | 400 |
| 2025-01-17 | 2026-01-17 | 200 |
| 2025-02-15 | 2026-02-15 | 950 |
SELECT 5