PostgreSQL：插入smallint数组前获取表中已存在的重复值

假设你的表名为test_table，数组列名为array_col（smallint类型数组），要在插入新数据前找出新数组中已存在于表中的具体值，可以按以下方式实现：

1. 直接查询重复值 #

使用unnest展开数组，结合INTERSECT获取新数组与表中所有数组元素的交集：

-- 替换new_arr为你要验证的新数组
SELECT ARRAY(
    SELECT unnest('{2,5,10}'::smallint[])
    INTERSECT
    SELECT unnest(array_col)
    FROM test_table
) AS duplicate_values;

执行后会返回所有重复的元素，无重复则返回空数组{}。

2. 封装为可复用函数 #

把逻辑封装成函数，方便调用：

CREATE OR REPLACE FUNCTION find_duplicate_array_values(new_arr smallint[])
RETURNS smallint[] AS $$
BEGIN
    RETURN ARRAY(
        SELECT unnest(new_arr)
        INTERSECT
        SELECT unnest(array_col)
        FROM test_table
    );
END;
$$ LANGUAGE plpgsql STABLE;

调用示例：

-- 查询新数组[2,5,10]中的重复值
SELECT find_duplicate_array_values('[2,5,10]'::smallint[]);

3. 插入前自动验证（触发器） #

如果需要在插入数据时自动触发验证并抛出错误，可创建触发器：

第一步：创建触发器函数 #

CREATE OR REPLACE FUNCTION check_array_duplicates()
RETURNS TRIGGER AS $$
DECLARE
    duplicates smallint[];
BEGIN
    duplicates := find_duplicate_array_values(NEW.array_col);
    IF duplicates <> '{}'::smallint[] THEN
        RAISE EXCEPTION '数组包含已存在的值: %', duplicates;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到表 #

CREATE TRIGGER trigger_check_array_duplicates
BEFORE INSERT ON test_table
FOR EACH ROW EXECUTE FUNCTION check_array_duplicates();

之后插入包含重复值的数据时，会直接抛出异常并显示具体的重复值。

性能优化建议 #

如果表数据量较大，建议给数组列创建GIN索引，提升交集查询的效率：

CREATE INDEX idx_test_table_array_col ON test_table USING GIN (array_col);

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