Oracle转PostgreSQL14分区表唯一索引报错解决方案咨询

问题场景 #

正在将Oracle分区表迁移至PostgreSQL14，原Oracle表上的全局唯一索引定义如下：

CREATE UNIQUE INDEX COL_IDX_ID
     ON TABLE1(COL_ID,ERROR_TIME_DT,ERROR_TIME_TM);

迁移时编写的PostgreSQL分区表脚本执行时触发报错：

ERROR: unique constraint on partitioned table must include all partitioning columns

尝试在中间分区（一级分区）创建相同索引仍无法解决，希望找到无需修改原索引列结构的解决办法。

PostgreSQL分区表脚本片段：

create table TABLE1
(
    COL_DT date constraint N_COL_DT not null
    ,COL_TM numeric(9) constraint N_COL_TM not null
    ,COL_INPROC_ID numeric(1) constraint N_COL_INPROC_ID not null
    ,COL_SUB_ID numeric(7) constraint N_COL_SUB_ID not null
    ,COL_RECORD_DT date constraint N_COL_RECORD_DT not null
    ,COL_RECORD_TM numeric(9) constraint N_COL_RECORD_TM not null
    ,COL_TIMEOFFSET varchar(6) constraint N_COL_TIMEOFFSET not null
    ,COL_TYPE numeric(1) constraint N_COL_TYPE not null
    ,COL4 numeric(1) DEFAULT 0 not null
    ,ERROR_TIME_DT DATE
     ,ERROR_TIME_TM numeric(9)
    ,ERROR__ID numeric(6)
    ,COL_ID VARCHAR(250)
    ,CONSTRAINT PK_COL primary key (COL_DT,COL_TM,COL_INPROC_ID,COL_SUB_ID) using index tablespace ${TABLESPACE_INDEX}
)   partition by range (COL_DT);


CREATE TABLE P_TABLE1_1 PARTITION OF TABLE1 FOR VALUES FROM (MINVALUE) TO (to_date('${PARTITION_DATE_LIMIT}', 'YYYYMM')) partition by list (COL_INPROC_ID);

CREATE TABLE P_TABLE1_1_P0 PARTITION OF P_TABLE1_1 FOR VALUES IN (0) ;
CREATE TABLE P_TABLE1_1_P1 PARTITION OF P_TABLE1_1 FOR VALUES IN (1) ;


CREATE TABLE PMAXVALUE PARTITION OF TABLE1 FOR VALUES FROM (to_date('${PARTITION_DATE_LIMIT}', 'YYYYMM')) TO (MAXVALUE) partition by list (COL_INPROC_ID);

CREATE TABLE PMAXVALUE_P0 PARTITION OF PMAXVALUE FOR VALUES IN (0) ;
CREATE TABLE PMAXVALUE_P1 PARTITION OF PMAXVALUE FOR VALUES IN (1) ;

无需修改索引结构的解决办法 #

方法：在最底层叶子分区创建唯一索引 #

PostgreSQL要求分区表的唯一约束/索引必须包含分区键，但叶子分区本身是具体的分区实例，不再包含子分区，因此可以直接在每个叶子分区上创建和原Oracle完全一致的唯一索引，无需添加分区键：

-- 为每个叶子分区创建唯一索引
CREATE UNIQUE INDEX COL_COLID_P0 ON P_TABLE1_1_P0 (ERROR_TIME_DT, ERROR_TIME_TM, COL_ID);
CREATE UNIQUE INDEX COL_COLID_P1 ON P_TABLE1_1_P1 (ERROR_TIME_DT, ERROR_TIME_TM, COL_ID);

CREATE UNIQUE INDEX COL_ID_PMAX_P0 ON PMAXVALUE_P0 (ERROR_TIME_DT, ERROR_TIME_TM, COL_ID);
CREATE UNIQUE INDEX COL_ID_PMAX_P1 ON PMAXVALUE_P1 (ERROR_TIME_DT, ERROR_TIME_TM, COL_ID);

逻辑等价性说明 #

每个叶子分区的COL_DT范围和COL_INPROC_ID值是固定且唯一的，不同叶子分区之间的ERROR_TIME_DT, ERROR_TIME_TM, COL_ID组合不会产生全局冲突，最终实现的效果和Oracle的全局唯一索引完全一致，无需修改原索引的列结构，也不会影响业务逻辑。

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