I am creating declarative partitions like:
CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY LIST (my_feature1);
And using some ad-hoc functions:
CREATE TABLE partitions.%I PARTITION OF cores.rents_core FOR VALUES IN (%L) PARTITION BY LIST (my_feature2)
CREATE TABLE partitions.%I PARTITION OF partitions.%I FOR VALUES IN (%L)
The partitions get created and populated successfully once the data get inserted.
However, when I create the table at the beginning, if I try to add an index like suggested in this Postgres official documentation resource:
Create an index on the key column(s), as well as any other indexes you might want for every partition. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)
like a spatial index:
CREATE INDEX "mydata_my_features_geom_idx" ON mydata.my_features using gist(geom);
I get an error like:
ERROR: cannot create index on partitioned table "my_features"
Is that actually possible add this kind of index or I am doing something wrong?