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?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
SHOW timezone;
Select current_date, now();
| TimeZone |
|---|
| UTC |
SHOW
| current_date | now |
|---|---|
| 2024-12-10 | 2024-12-10 01:51:31.798543+00 |
SELECT 1
with cte(days) as
( select *
from generate_series( date_trunc('month','2023-06-30 09:54:23.000'::timestamp)
, current_date-interval '1 year'
,'1 month'
)
) -- select * from cte
select tsrange(days::timestamp(0)
, least ((days+interval '1 month ')::timestamp(0)
, (current_date-interval '1 year - 1 day')::timestamp(0)
)
,'[)') start_end_dates
from cte
order by days desc;
| start_end_dates |
|---|
| ["2023-12-01 00:00:00","2023-12-11 00:00:00") |
| ["2023-11-01 00:00:00","2023-12-01 00:00:00") |
| ["2023-10-01 00:00:00","2023-11-01 00:00:00") |
| ["2023-09-01 00:00:00","2023-10-01 00:00:00") |
| ["2023-08-01 00:00:00","2023-09-01 00:00:00") |
| ["2023-07-01 00:00:00","2023-08-01 00:00:00") |
| ["2023-06-01 00:00:00","2023-07-01 00:00:00") |
SELECT 7