CREATE TABLE foo ( id INT NOT NULL, ts TIMESTAMP WITH TIME ZONE NOT NULL
) PARTITION BY RANGE (ts);
CREATE TABLE foo_2023 ( id INT NOT NULL, ts TIMESTAMP WITH TIME ZONE NOT NULL
);
ALTER TABLE ONLY foo ATTACH PARTITION foo_2023 FOR VALUES FROM ('2023-01-01 00:00:00+09') TO ('2024-01-01 00:00:00+09');
CREATE UNIQUE INDEX pk_foo ON ONLY foo USING btree (id, ts);
ALTER TABLE ONLY foo REPLICA IDENTITY USING INDEX pk_foo;
CREATE UNIQUE INDEX foo_2023_id_ts_ix ON foo_2023 USING btree (id, ts);
ALTER INDEX pk_foo ATTACH PARTITION foo_2023_id_ts_ix;
This example confused me quite a bit when I first read it. I think that the documentation for CREATE INDEX .. ONLY is pretty inadequate. All it says is "Indicates not to recurse creating indexes on partitions, if the table is partitioned. The default is to recurse." But that would just create a permanently empty index, which is of no use to anyone. I think we should somehow explain the intent of this, namely that this creates an initially invalid index which can be made valid by using ALTER INDEX ... ATTACH PARTITION once per partition.
