create-table-and-partitions.sql

create table t (
  id bigint generated always as identity not null,
  created_at timestamptz not null)
partition by range (created_at);

create index t__id__created_at__idx
  on t(id, created_at);


-- partition for next month

create table t_2025_02 (
  like t including all excluding indexes excluding identity
);

create index t_2025_02__id__created_at__idx
  on t_2025_02(id, created_at);

alter table t
  attach partition t_2025_02
    for values from ('2025-02-01') to ('2025-03-01');

-- looking at the top-level index

postgres=# \d+ t__id__created_at__idx
                  Partitioned index "public.t__id__created_at__idx"
┌────────────┬──────────────────────────┬──────┬────────────┬─────────┬──────────────┐
│   Column   │           Type           │ Key? │ Definition │ Storage │ Stats target │
├────────────┼──────────────────────────┼──────┼────────────┼─────────┼──────────────┤
│ id         │ bigint                   │ yes  │ id         │ plain   │              │
│ created_at │ timestamp with time zone │ yes  │ created_at │ plain   │              │
└────────────┴──────────────────────────┴──────┴────────────┴─────────┴──────────────┘
btree, for table "public.t"
Partitions: t_2025_02__id__created_at__idx
Access method: btree