PostgreSQL 14.1非键列分区报错，求不破坏完整性的解决方案

开发者阿泽

2026-6-14

PostgreSQL 14.1 非键列分区与唯一约束冲突解决

背景

使用PostgreSQL 14.1版本，具备Oracle使用经验
事件跟踪表每日产生200-300万条数据，需按as_of_dt（start_tmstp的日期部分）做日分区，目标是提升查询速度、通过删除分区替代DELETE操作清理旧数据（数据保留1个季度）
表采用event_id作为代理主键，关联子表；同时存在唯一键约束，需保留数据完整性

触发报错

执行分区DDL时出现如下错误：

ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "event_tracking_partition_test" lacks column "as_of_dt" which is part of the partition key.
SQL state: 0A000

测试用DDL

CREATE TABLE IF NOT EXISTS process_tracking_data.event_tracking_partition_test
(
    event_id uuid NOT NULL,
    event_num numeric,
    event_type_id uuid NOT NULL,
    entity_id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    entity_type character varying(100) COLLATE pg_catalog."default",
    message_id character varying(500) COLLATE pg_catalog."default",
    event_data character varying(500) COLLATE pg_catalog."default",
    event_status character varying(30) COLLATE pg_catalog."default" NOT NULL,
    as_of_dt date NOT NULL,
    start_tmstp timestamp without time zone NOT NULL,
    end_tmstp timestamp without time zone,
    source_event_id character varying(250) COLLATE pg_catalog."default",
    err_msg_cd character varying(50) COLLATE pg_catalog."default",
    err_msg_desc character varying(2000) COLLATE pg_catalog."default",
    created_by character varying(50) COLLATE pg_catalog."default" NOT NULL,
    created_tmstp timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_by character varying(50) COLLATE pg_catalog."default" NOT NULL,
    last_updated_tmstp timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_event_tracking_partition_test PRIMARY KEY (event_id),
    CONSTRAINT uk_event_tracking_partition_test UNIQUE (event_type_id, entity_id, entity_type, start_tmstp)
)
PARTITION BY RANGE (as_of_dt);

CREATE TABLE process_tracking_data.event_tracking_partition_test_part1 PARTITION OF process_tracking_data.event_tracking_partition_test
    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE process_tracking_data.event_tracking_partition_test_part2 PARTITION OF process_tracking_data.event_tracking_partition_test
    FOR VALUES FROM ('2023-01-01') TO ('2026-12-31');

可行解决方案

方案1：将分区键纳入全局约束

PostgreSQL强制要求分区表的唯一约束（含主键）必须包含所有分区键，以此保证跨分区的唯一性。由于event_id是全局唯一的代理键，start_tmstp本身包含as_of_dt的日期信息，将as_of_dt加入约束不会破坏实际唯一性，仅满足语法要求：

主键调整为(event_id, as_of_dt)
唯一约束调整为(event_type_id, entity_id, entity_type, start_tmstp, as_of_dt)

修改后的约束定义片段：

CONSTRAINT pk_event_tracking_partition_test PRIMARY KEY (event_id, as_of_dt),
CONSTRAINT uk_event_tracking_partition_test UNIQUE (event_type_id, entity_id, entity_type, start_tmstp, as_of_dt)

注意：关联event_id的子表外键无需修改，查询时仅使用event_id即可，PostgreSQL优化器会自动处理分区键的匹配逻辑。

方案2：分区级局部约束替代全局约束

如果不想修改全局主键结构，可以移除父表的全局约束，在每个分区上单独创建主键和唯一约束：

创建无全局约束的分区表：

CREATE TABLE IF NOT EXISTS process_tracking_data.event_tracking_partition_test
(
    event_id uuid NOT NULL,
    event_num numeric,
    event_type_id uuid NOT NULL,
    entity_id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    entity_type character varying(100) COLLATE pg_catalog."default",
    message_id character varying(500) COLLATE pg_catalog."default",
    event_data character varying(500) COLLATE pg_catalog."default",
    event_status character varying(30) COLLATE pg_catalog."default" NOT NULL,
    as_of_dt date NOT NULL,
    start_tmstp timestamp without time zone NOT NULL,
    end_tmstp timestamp without time zone,
    source_event_id character varying(250) COLLATE pg_catalog."default",
    err_msg_cd character varying(50) COLLATE pg_catalog."default",
    err_msg_desc character varying(2000) COLLATE pg_catalog."default",
    created_by character varying(50) COLLATE pg_catalog."default" NOT NULL,
    created_tmstp timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_by character varying(50) COLLATE pg_catalog."default" NOT NULL,
    last_updated_tmstp timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (as_of_dt);

创建分区时添加局部约束：

CREATE TABLE process_tracking_data.event_tracking_partition_test_part1 PARTITION OF process_tracking_data.event_tracking_partition_test
    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31')
    CONSTRAINT pk_part1 PRIMARY KEY (event_id),
    CONSTRAINT uk_part1 UNIQUE (event_type_id, entity_id, entity_type, start_tmstp);

优缺点：

优点：子表外键无需修改，约束逻辑更贴近原有设计
缺点：无法通过数据库强制全局唯一性，需依赖应用层保证event_id不重复；新增分区时需手动添加约束（可通过脚本自动化）

方案3：组合分区（备选）

若查询经常结合event_type_id和as_of_dt，可采用LIST-RANGE组合分区：先按event_type_id做LIST分区，再在每个LIST分区内按as_of_dt做RANGE子分区。此时分区键为(event_type_id, as_of_dt)，主键需包含这两个字段，适合匹配特定查询模式的场景。

PostgreSQL 14.1非键列分区报错，求不破坏完整性的解决方案

背景 #

触发报错 #

测试用DDL #

可行解决方案 #

方案1：将分区键纳入全局约束 #

方案2：分区级局部约束替代全局约束 #

方案3：组合分区（备选） #

推荐方案 #

背景