PostgreSQL按时间戳范围分区非唯一键冲突问题解决方案咨询

问题根因 #

你对PostgreSQL分区表的约束规则存在误解：PostgreSQL仅要求如果要为分区表创建主键或全局唯一约束，约束字段必须包含所有分区键，并不强制要求必须把分区键单独设为主键，也不强制要求分区表必须创建主键。你当前将CreationDate单独设为主键，自然会因为原表存在重复时间戳触发唯一约束冲突。

可行解决方案 #

方案1：调整主键为复合主键（推荐） #

将原业务中全局唯一的Id字段和分区键CreationDate组合为复合主键，既满足分区表的约束要求，又不会触发重复键冲突：

CREATE TABLE "UserFavorites_master" (
    "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "UserId" int4 NOT NULL,
    "CardId" int4 NOT NULL,
    "CreationDate" timestamp NOT NULL,
    -- 复合主键包含分区键+全局唯一业务字段
    CONSTRAINT "PK_UserFavorites_Id_CreationDate" PRIMARY KEY ("Id", "CreationDate")
) partition by range ("CreationDate");

该方案不需要修改任何现有业务逻辑，完全兼容原表数据。

方案2：直接移除主键约束 #

如果你的业务场景不需要主键约束，可以直接删除主键定义，PostgreSQL分区表本身没有强制要求必须创建主键：

CREATE TABLE "UserFavorites_master" (
    "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "UserId" int4 NOT NULL,
    "CardId" int4 NOT NULL,
    "CreationDate" timestamp NOT NULL
) partition by range ("CreationDate");

该方案完全符合你不希望给时间戳字段新增唯一约束的需求。

补充说明 #

如果后续需要为分区表创建其他全局唯一约束，都必须将分区键CreationDate加入到约束字段中，这是PostgreSQL分区表的固有规则，无法绕过。
修改主表定义后，其余迁移代码无需调整，重新执行即可完成数据迁移，不会再触发重复键错误。

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