PostgreSQL中如何禁止同一值跨多列重复插入？

要确保同一值无法出现在表的多个列（如示例中的col1和col2）的任何位置，可以通过以下两种方式实现：

方法一：使用触发器（直观易维护） #

1. 创建基础表 #

CREATE TABLE your_table (
    col1 TEXT NOT NULL,
    col2 TEXT NOT NULL
);

2. 创建触发器函数 #

该函数会在插入或更新数据前，检查新值是否已存在于表的任意列中：

CREATE OR REPLACE FUNCTION check_unique_across_columns()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查col1的新值是否已存在于任意列
    IF EXISTS (
        SELECT 1 FROM your_table
        WHERE col1 = NEW.col1 OR col2 = NEW.col1
    ) THEN
        RAISE EXCEPTION '值 "%" 已存在于表的col1或col2列中', NEW.col1;
    END IF;
    -- 检查col2的新值是否已存在于任意列
    IF EXISTS (
        SELECT 1 FROM your_table
        WHERE col1 = NEW.col2 OR col2 = NEW.col2
    ) THEN
        RAISE EXCEPTION '值 "%" 已存在于表的col1或col2列中', NEW.col2;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器 #

将触发器函数绑定到表的插入和更新操作：

CREATE TRIGGER trigger_check_unique_across_columns
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION check_unique_across_columns();

方法二：使用排他约束（性能更优） #

利用PostgreSQL的排他约束结合数组交集检查，需要先安装btree_gist扩展以支持TEXT数组的索引操作：

1. 安装必要扩展 #

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 创建表并添加排他约束 #

通过数组将两列值组合，约束确保新行的数组与已有行的数组无交集：

CREATE TABLE your_table (
    col1 TEXT NOT NULL,
    col2 TEXT NOT NULL,
    EXCLUDE USING gist (ARRAY[col1, col2] WITH &&)
);

测试验证 #

• 插入合法数据：

  INSERT INTO your_table VALUES ('ABC', 'DEF'); -- 执行成功
  

• 插入重复值数据：

  INSERT INTO your_table VALUES ('XYZ', 'ABC'); -- 执行失败，触发约束/触发器抛出异常
  

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