Postgres触发器中如何对比数组变更（识别新增/移除元素）

在PostgreSQL触发器里，你可以直接通过OLD和NEW记录获取数组更新前后的状态，再利用内置的数组和集合操作函数快速识别新增、移除的元素。

核心思路 #

• 新增元素：取更新后数组（NEW.数组列）中有，但更新前数组（OLD.数组列）中没有的元素
• 移除元素：取更新前数组中有，但更新后数组中没有的元素

基础实现示例 #

假设你的表名为schedule，数组列名为days（类型为text[]），以下是完整的触发器函数和触发器创建代码：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION track_days_changes()
RETURNS TRIGGER AS $$
DECLARE
    added_days text[];
    removed_days text[];
BEGIN
    -- 处理数组为NULL的边界情况
    IF OLD.days IS NULL THEN
        added_days := NEW.days;
        removed_days := '{}'::text[];
    ELSIF NEW.days IS NULL THEN
        added_days := '{}'::text[];
        removed_days := OLD.days;
    ELSE
        -- 计算新增的日期
        added_days := array(SELECT unnest(NEW.days) EXCEPT SELECT unnest(OLD.days));
        -- 计算移除的日期
        removed_days := array(SELECT unnest(OLD.days) EXCEPT SELECT unnest(NEW.days));
    END IF;

    -- 这里可以替换为你的业务逻辑，比如插入日志表、触发其他操作
    RAISE NOTICE '新增日期: %, 移除日期: %', added_days, removed_days;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

CREATE TRIGGER schedule_days_update_trigger
BEFORE UPDATE OF days ON schedule
FOR EACH ROW
EXECUTE FUNCTION track_days_changes();

处理重复元素的场景 #

如果数组中存在重复元素（比如OLD.days = ['Mon', 'Mon']，NEW.days = ['Mon']），上面的EXCEPT会自动去重，导致无法识别重复项的变化。这种情况下需要改用EXCEPT ALL：

-- 计算新增（含重复项差异）
added_days := array(SELECT unnest(NEW.days) EXCEPT ALL SELECT unnest(OLD.days));
-- 计算移除（含重复项差异）
removed_days := array(SELECT unnest(OLD.days) EXCEPT ALL SELECT unnest(NEW.days));

验证效果 #

用你给出的测试案例：

• 更新前OLD.days = ['Mon','Tue','Wed','Sun']
• 更新后NEW.days = ['Mon','Tue','Wed','Thu','Fri']

触发器会输出：

NOTICE:  新增日期: {Thu,Fri}, 移除日期: {Sun}

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