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
WITH cte AS (
SELECT collected_date, SUM(collected_volume) AS collected_volume
FROM vouchers
GROUP BY collected_date
)
SELECT collected_date, collected_volume FROM cte
UNION ALL
SELECT collected_date, -1.0*collected_volume FROM cte
ORDER BY collected_date, collected_volume DESC;
| collected_date | collected_volume |
|---|---|
| 2024-03-09 | 900 |
| 2024-03-09 | -900.0 |
| 2024-04-20 | 1100 |
| 2024-04-20 | -1100.0 |
| 2024-05-24 | 400 |
| 2024-05-24 | -400.0 |
| 2025-01-17 | 200 |
| 2025-01-17 | -200.0 |
| 2025-02-15 | 950 |
| 2025-02-15 | -950.0 |
SELECT 10