PostgreSQL触发器开发：校验JSONB数组内值不重复

别担心，我来帮你搞定这个PostgreSQL触发器的问题！你的需求是确保JSONB字段data里的ids数组中的值不会在其他商户记录中重复，这个思路用触发器实现完全可行，我来帮你修正查询语句并完善整个触发器函数。

完整的触发器函数实现 #

首先，我们需要处理两种场景：插入新记录和更新已有记录（更新时要排除当前记录本身，避免误判自己原有的ID为重复），同时还可以额外加个校验，确保单条记录的ids数组内部没有重复值。

CREATE OR REPLACE FUNCTION validate_id_constraint() 
RETURNS TRIGGER AS $$
DECLARE
    duplicate_exists BOOLEAN;
BEGIN
    -- 先校验data.ids是否为合法的JSON数组
    IF jsonb_typeof(NEW.data->'ids') <> 'array' THEN
        RAISE EXCEPTION '字段data.ids必须是JSON数组类型';
    END IF;

    -- 检查是否有其他商户记录的data.ids包含新记录中的任意ID
    SELECT EXISTS (
        SELECT 1
        FROM merchants m
        -- 更新时排除当前记录，插入时NEW.key是新值，自然不会匹配到已有记录
        WHERE m.key <> COALESCE(NEW.key, m.key)
        -- ?| 操作符：检查JSONB数组是否包含右侧文本数组中的任意元素
        AND m.data->'ids' ?| array(SELECT jsonb_array_elements_text(NEW.data->'ids'))
    ) INTO duplicate_exists;

    IF duplicate_exists THEN
        RAISE EXCEPTION 'data.ids中的一个或多个ID已存在于其他商户记录中';
    END IF;

    -- 可选：校验单条记录的data.ids内部是否有重复值
    SELECT EXISTS (
        SELECT id
        FROM jsonb_array_elements_text(NEW.data->'ids') AS id
        GROUP BY id
        HAVING COUNT(*) > 1
    ) INTO duplicate_exists;

    IF duplicate_exists THEN
        RAISE EXCEPTION 'data.ids数组内部存在重复值';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器 #

接下来需要把这个函数绑定到merchants表的INSERT和UPDATE操作上：

CREATE TRIGGER merchants_id_unique_trigger
BEFORE INSERT OR UPDATE OF data ON merchants
FOR EACH ROW EXECUTE FUNCTION validate_id_constraint();

关键部分解释 #

• 排除当前记录：COALESCE(NEW.key, m.key)在插入时，NEW.key是新生成的UUID，所以m.key <> NEW.key会匹配所有已有记录；在更新时，NEW.key是当前记录的主键，会自动排除自己，避免把原有的ID误判为重复。
• ?|操作符：这是PostgreSQL专门为JSONB数组设计的操作符，用来检查数组是否包含右侧文本数组中的任意元素，比循环遍历高效得多。
• 数组展开：jsonb_array_elements_text(NEW.data->'ids')把JSONB数组转换成文本行，方便我们做后续的校验。

测试一下 #

你可以插入一条测试数据验证效果：

-- 插入第一条记录，正常执行
INSERT INTO merchants (key, data) VALUES (gen_random_uuid(), '{"ids": ["1001", "1002"]}');

-- 插入第二条包含重复ID的记录，会触发异常
INSERT INTO merchants (key, data) VALUES (gen_random_uuid(), '{"ids": ["1001", "1003"]}');

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