When the parent table of a partitioned table has a unique index, it seems that the child tables of the partitioned table no longer need an explicit unique index

[openai0229]

This is parent table ddl：

create table measurement
(
measurement_id integer default nextval('test.measurement_measurement_id_seq'::regclass) not null,
sensor_id integer not null,
reading double precision not null,
recorded_at timestamp not null,
constraint measurement_pkey
primary key (measurement_id, recorded_at)
)
partition by RANGE (recorded_at);

This is child table ddl：

CREATE TABLE test.measurement_y2024m01 PARTITION OF test.measurement FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2024-02-01 00:00:00') TABLESPACE pg_default;
CREATE UNIQUE INDEX measurement_y2024m01_pkey ON test.measurement_y2024m01 USING btree (measurement_id, recorded_at) TABLESPACE pg_default;

If I run this SQL statement, I will receive a prompt saying that measurement_y2024m01_pkey already exists

[MichaelDBA]

Interesting... PG v10 will not let you create a primary key on the parent table. It can only be created on the partitions. So the child table ddl you showed above would work correctly on PG v10, but the primary key definition on the parent would fail. For subsequent versions, you can create the primary key on the partitions if you have not already created it on the parent. Once you have done that, the primary key would not show up on the parent, but on the children DDL only. But if you create the primary key on the parent at the getgo, it would also show up on the DDL for the partitions under it as well.

So there are 2 things that might be addressed:

1. Handle PG v10 differently.
2. Handle subsequent versions in one or more ways...

I need to think about this.

[MichaelDBA]

I think the solution is to just change the DDL for creating the PKEY on the partitions by adding keyword, IF NOT EXISTS
CREATE UNIQUE INDEX IF NOT EXISTS measurement_y2024m01_pkey ON measurement_y2024m01 USING btree (measurement_id, recorded_at) TABLESPACE pg_default;

Seems OK with you?

[MichaelDBA]

Actually, this is a bug since I already do this for non-unique indexes but not for unique ones!
Fixing it and testing it now...

[openai0229]

Yes, my solution is to add if not exists

[MichaelDBA]

fix is committed, closing this issue now.