PostgreSQL跨列自定义约束批量插入失效问题及优化咨询

我有一张用于ID映射的表demo_table，结构如下：

需要添加的约束规则：若某ID已存在于id2列，则不允许将其插入id1列，但id1与id2值相等的情况除外（如上表最后一行）。

我编写了如下自定义函数作为约束逻辑：

CREATE OR REPLACE FUNCTION public.chain_mapping_constraint(pid1 BIGINT, pid2 BIGINT)
RETURNS bool AS
$$
SELECT CASE WHEN pid1 = pid2 THEN TRUE
            WHEN (SELECT COUNT(t.*) FROM demo_table t WHERE t.id2 = pid1) > 0 THEN FALSE
            ELSE TRUE
       END;
$$  LANGUAGE sql STABLE

创建表后，通过以下语句添加约束：

ALTER TABLE demo_table ADD CONSTRAINT chain_mapping_constraint
CHECK (public.chain_mapping_constraint(id1, id2)) NOT VALID;

该约束在单条插入记录时有效，但执行批量插入（如下述从其他表查询插入的语句）时会失效：

INSERT INTO demo_table(id1, id2)
SELECT id1, id2
FROM some_other_table

原因是约束仅检查表中已存在的数据，未检查本次批量插入的待提交数据，导致违反规则的记录被插入。现咨询两个问题：

1. 如何让约束同时校验本次插入的待提交数据？
2. 是否有更优的实现方式？

1. 让约束覆盖待提交数据的办法 #

CHECK约束里的函数只能访问表中已提交的数据，无法触达本次批量插入的待处理行。要解决这个问题，必须改用触发器实现逻辑——触发器能访问到本次插入的所有待提交数据。

方案A：行级触发器（适配单条+批量插入） #

先编写触发器函数：

CREATE OR REPLACE FUNCTION public.chain_mapping_trigger_func()
RETURNS TRIGGER AS
$$
BEGIN
    -- 仅检查非自匹配的情况
    IF NEW.id1 != NEW.id2 THEN
        -- 同时校验已有数据和本次插入的其他行
        IF EXISTS (
            SELECT 1 FROM demo_table WHERE id2 = NEW.id1
            UNION ALL
            SELECT 1 FROM NEW_TABLE WHERE id2 = NEW.id1
        ) THEN
            RAISE EXCEPTION 'ID %不能插入到id1列，因为它已存在于id2列（自匹配情况除外）', NEW.id1;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

再创建触发器：

CREATE TRIGGER chain_mapping_insert_trigger
BEFORE INSERT ON demo_table
FOR EACH ROW
EXECUTE FUNCTION public.chain_mapping_trigger_func();

方案B：语句级触发器（批量插入更高效） #

如果频繁处理大数据量批量插入，用语句级触发器一次性检查所有待插入行，性能更优：

CREATE OR REPLACE FUNCTION public.chain_mapping_bulk_trigger_func()
RETURNS TRIGGER AS
$$
DECLARE
    invalid_ids BIGINT[];
BEGIN
    -- 找出所有违规的id1
    SELECT ARRAY_AGG(DISTINCT nt.id1) INTO invalid_ids
    FROM NEW_TABLE nt
    WHERE nt.id1 != nt.id2
    AND EXISTS (
        SELECT 1 FROM demo_table dt WHERE dt.id2 = nt.id1
        UNION ALL
        SELECT 1 FROM NEW_TABLE nt2 WHERE nt2.id2 = nt.id1
    );

    IF invalid_ids IS NOT NULL AND array_length(invalid_ids, 1) > 0 THEN
        RAISE EXCEPTION '以下ID不能插入到id1列：%，因为它们已存在于id2列（自匹配情况除外）', invalid_ids;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER chain_mapping_bulk_insert_trigger
BEFORE INSERT ON demo_table
FOR EACH STATEMENT
EXECUTE FUNCTION public.chain_mapping_bulk_trigger_func();

2. 更优的实现方式 #

方式1：批量插入前先校验（性能最优） #

不用触发器，在执行INSERT前先运行查询验证待插入数据：

WITH to_insert AS (
    SELECT id1, id2 FROM some_other_table
)
SELECT id1 
FROM to_insert
WHERE id1 != id2
AND EXISTS (
    SELECT 1 FROM demo_table dt WHERE dt.id2 = to_insert.id1
    UNION ALL
    SELECT 1 FROM to_insert ti WHERE ti.id2 = to_insert.id1
);

如果查询返回结果，说明存在违规数据，直接取消插入；若无结果，再执行INSERT操作。这种方式没有触发器的额外开销，适合大数据量场景。

方式2：结合部分索引+CHECK约束 #

先创建部分索引标记所有非自匹配的id2值：

CREATE UNIQUE INDEX idx_non_self_id2 ON demo_table(id2) WHERE id1 != id2;

这个索引能避免同一个ID被多次作为非自匹配的id2值。再配合插入前的校验或修改后的CHECK约束，也能实现需求，且原生索引的性能比触发器更好。

方式3：PostgreSQL排除约束（高级特性） #

利用PostgreSQL的EXCLUDE约束实现复杂冲突检查，适合熟悉PostgreSQL高级特性的场景：

ALTER TABLE demo_table
ADD CONSTRAINT chain_mapping_exclude
EXCLUDE USING gist (
    id1 WITH =
) WHERE (id1 != id2 AND EXISTS (SELECT 1 FROM demo_table dt WHERE dt.id2 = id1));

不过该约束需要依赖gist索引，维护成本比触发器高，不如前两种方式直观。

综合来看，行级触发器是最通用的方案，能覆盖所有插入场景；批量插入前校验则是性能最优的选择，适合大数据量操作。

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