PostgreSQL分区表唯一约束报错：如何在分区列可重复时设置约束？

报错原因 #

PostgreSQL强制要求：分区表上的所有唯一约束（包括主键）必须包含全部分区键列。你当前的tracking_id唯一约束未包含分区键scan_time，因此触发报错。

解决方案 #

方案1：调整唯一约束包含分区键（适允tracking_id跨scan_time重复场景） #

如果业务允许同一个tracking_id在不同scan_time下存在，只需将scan_time加入tracking_id的唯一约束中：

CREATE TABLE tracking_trackingdata (
  "id"                  uuid                     NOT NULL,
  tracking_id           varchar(100)             NOT NULL,
  dynamic_url_object_id bigint                   NOT NULL,
  ip_address            inet                     NOT NULL,
  scan_time             timestamp with time zone NOT NULL,
  created               timestamp with time zone NOT NULL,
  modified              timestamp with time zone NOT NULL,

  PRIMARY KEY ( "id", scan_time ),
  UNIQUE (tracking_id, scan_time)
) PARTITION BY RANGE ( scan_time );

此方案既满足分区表的约束规则，又能保证同一scan_time周期内tracking_id的唯一性。

方案2：触发器保证tracking_id全局唯一（适需tracking_id全局唯一场景） #

如果业务要求tracking_id全局唯一，不允许跨scan_time重复，可通过触发器实现全局唯一性校验：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION check_tracking_id_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM tracking_trackingdata
    WHERE tracking_id = NEW.tracking_id
  ) THEN
    RAISE EXCEPTION 'tracking_id % already exists', NEW.tracking_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 在分区表上绑定触发器：

CREATE TRIGGER trigger_check_tracking_id_unique
BEFORE INSERT OR UPDATE ON tracking_trackingdata
FOR EACH ROW EXECUTE FUNCTION check_tracking_id_unique();

注意：数据量较大时，全分区扫描会带来性能损耗，需结合业务规模评估使用。

方案3：独立非分区表维护tracking_id唯一性 #

通过单独的非分区表存储全局唯一的tracking_id，再通过关联关系绑定分区表：

1. 创建唯一映射表：

CREATE TABLE tracking_id_lookup (
  tracking_id varchar(100) PRIMARY KEY,
  id uuid NOT NULL REFERENCES tracking_trackingdata(id)
);

2. 修改分区表结构：

CREATE TABLE tracking_trackingdata (
  "id"                  uuid                     NOT NULL,
  tracking_id           varchar(100)             NOT NULL REFERENCES tracking_id_lookup(tracking_id),
  dynamic_url_object_id bigint                   NOT NULL,
  ip_address            inet                     NOT NULL,
  scan_time             timestamp with time zone NOT NULL,
  created               timestamp with time zone NOT NULL,
  modified              timestamp with time zone NOT NULL,

  PRIMARY KEY ( "id", scan_time )
) PARTITION BY RANGE ( scan_time );

插入数据时需先向tracking_id_lookup表写入数据（利用主键约束保证全局唯一），再插入分区表数据。此方案性能更优，但需额外维护关联表。

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