如何将分区表父表的唯一索引转为主键且无需重建子表主键？

我有一个分区表，其子表已配置主键，父表上存在一个匹配的唯一索引，作为子表主键索引的父索引。我希望将父表的该唯一索引转换为主键约束，且无需重建所有子表的主键，但尝试的所有方法均失败，请问是否存在可行方案？

可复现示例 #

创建测试表与索引的SQL代码 #

create table tst (id int,partition_num int) partition by list (partition_num);
create table tst_1 partition of tst for values in (1);
create table tst_2 partition of tst for values in (2);
alter table tst_1 add constraint tst_pk_1 primary key (id,partition_num);
alter table tst_2 add constraint tst_pk_2 primary key (id,partition_num);
create unique index tst_ui1 on tst (id,partition_num);

-- 验证子表主键是分区索引
select relispartition from pg_class where relname = 'tst_pk_1'; -- 返回 true

当前表结构查询结果 #

父表tst结构

dbname=> \d+ tst
                                        Partitioned table "public.tst"
    Column     |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id            | integer |           |          |         | plain   |             |              |
 partition_num | integer |           |          |         | plain   |             |              |
Partition key: LIST (partition_num)
Indexes:
    "tst_ui1" UNIQUE, btree (id, partition_num)
Partitions: tst_1 FOR VALUES IN (1),
            tst_2 FOR VALUES IN (2)

子表tst_1结构

dbname=> \d+ tst_1
                                             Table "public.tst_1"
    Column     |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id            | integer |           | not null |         | plain   |             |              |
 partition_num | integer |           | not null |         | plain   |             |              |
Partition of: tst FOR VALUES IN (1)
Partition constraint: ((partition_num IS NOT NULL) AND (partition_num = 1))
Indexes:
    "tst_pk_1" PRIMARY KEY, btree (id, partition_num)
Access method: heap

存在可行方案，无需重建子表主键，只需按以下步骤操作：

1. 删除父表上的唯一索引

   DROP INDEX tst_ui1;
   

2. 在父表上创建主键约束

   ALTER TABLE tst ADD PRIMARY KEY (id, partition_num);
   

验证结果 #

执行完成后，查询父表结构：

dbname=> \d+ tst
                                        Partitioned table "public.tst"
    Column     |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id            | integer |           | not null |         | plain   |             |              |
 partition_num | integer |           | not null |         | plain   |             |              |
Partition key: LIST (partition_num)
Indexes:
    "tst_pkey" PRIMARY KEY, btree (id, partition_num)
Partitions: tst_1 FOR VALUES IN (1),
            tst_2 FOR VALUES IN (2)

查询子表tst_1结构，可见原主键tst_pk_1已自动关联为父表主键的分区索引，未被重建：

dbname=> \d+ tst_1
                                             Table "public.tst_1"
    Column     |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id            | integer |           | not null |         | plain   |             |              |
 partition_num | integer |           | not null |         | plain   |             |              |
Partition of: tst FOR VALUES IN (1)
Partition constraint: ((partition_num IS NOT NULL) AND (partition_num = 1))
Indexes:
    "tst_pk_1" PRIMARY KEY, btree (id, partition_num)
    "tst_pkey" PRIMARY KEY, btree (id, partition_num) -- 自动关联的父表主键分区索引
Access method: heap

原理说明 #

PostgreSQL允许在分区父表上创建主键约束，当子表已存在与父表主键定义完全匹配的主键时，会自动将子表的主键关联为父表主键的分区索引，无需重建子表的索引结构。之前尝试失败的原因通常是直接使用ALTER TABLE ... ADD PRIMARY KEY USING INDEX命令，该命令会尝试创建新的索引，与子表已有的主键冲突；而先删除父表的唯一索引，再直接创建父表主键，就能利用PostgreSQL的分区主键关联机制，复用子表已有的主键索引。

内容的提问来源于stack exchange，提问作者Peter