从Oracle迁移至PostgreSQL14时分区表创建报错咨询

错误原因 #

你遇到的问题本质是PostgreSQL与Oracle在分区表索引管理上的核心差异：
PostgreSQL中所有分区表（包括主分区表、子分区表）都不允许设置默认表空间，而你在主表TABLE1的主键约束里通过using index tablespace ${TABLESPACE_INDEX}试图给分区表的全局主键索引指定表空间——但分区表的主键索引属于分区索引（每个子分区对应一个独立的索引分片），主表层面无法直接为这类索引指定表空间，必须在子分区级别单独配置。

修复方案 #

步骤1：修改主表创建脚本，移除索引的表空间指定

去掉主表主键约束中的表空间指定语句，修改后的脚本如下：

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_RECORD_DT not null
    ,COL_RECORD_TM numeric(9) constraint N_RECORD_TM not null
    ,COL_TIMEOFFSET varchar(6) constraint N_TIMEOFFSET not null
    ,COL_TYPE numeric(1) constraint N_TYPE not null
    ,COL4 numeric(1) DEFAULT 0 not null
    ,CONSTRAINT PK_COL primary key (COL_DT,COL_TM,COL_INPROC_ID,COL_SUB_ID)
)   partition by range (COL_DT);

步骤2：为子分区配置表空间（两种可选方式）

方式1：子分区指定表空间，索引自动继承

创建子分区时直接指定表空间，PostgreSQL默认会让分区的索引继承该表空间：

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) 
TABLESPACE ${TABLESPACE_DATA};

CREATE TABLE P_TABLE1_1_P1 PARTITION OF P_TABLE1_1 
FOR VALUES IN (1) 
TABLESPACE ${TABLESPACE_DATA};

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) 
TABLESPACE ${TABLESPACE_DATA};

CREATE TABLE PMAXVALUE_P1 PARTITION OF PMAXVALUE 
FOR VALUES IN (1) 
TABLESPACE ${TABLESPACE_DATA};

方式2：单独为每个子分区的主键索引指定表空间

如果需要数据和索引使用不同的表空间，可在子分区创建完成后，单独修改索引的表空间：

-- 先通过\d命令查看子分区的主键索引名，例如P_TABLE1_1_P0的索引名格式通常为PK_COL_p_table1_1_p0_idx
\d P_TABLE1_1_P0

-- 逐个修改索引表空间
ALTER INDEX PK_COL_p_table1_1_p0_idx TABLESPACE ${TABLESPACE_INDEX};
ALTER INDEX PK_COL_p_table1_1_p1_idx TABLESPACE ${TABLESPACE_INDEX};
ALTER INDEX PK_COL_pmaxvalue_p0_idx TABLESPACE ${TABLESPACE_INDEX};
ALTER INDEX PK_COL_pmaxvalue_p1_idx TABLESPACE ${TABLESPACE_INDEX};

关键差异说明 #

Oracle允许在分区表级别统一指定索引表空间，但PostgreSQL的分区索引是完全分布式的——每个子分区对应独立的索引分片，因此必须在子分区层面单独配置表空间，主分区表无法设置任何默认表空间属性。

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