PostgreSQL含反斜杠JSONB对象的更新删除报错解决方案咨询

问题背景 #

数据库表mytable2中存在名称包含反斜杠的记录，这类记录可正常插入，但通过名称字段执行更新或删除操作时，PostgreSQL会抛出JSON语法错误，导致UI端操作失败。

错误原因分析 #

问题根源在于触发器函数enforce_mytable1_unused的实现：

servicesJson := concat('{"', OLD.name, '":{"enabled":"1"}}');

当OLD.name包含反斜杠（如New\protection\plan）时，直接用concat拼接出的字符串不符合JSON语法规范——JSON要求反斜杠必须转义为\\，而未转义的\p会被判定为无效转义序列，最终在转换为jsonb时触发报错。

解决方案 #

替换手动拼接JSON字符串的方式，使用PostgreSQL内置的jsonb_build_object函数构造JSONB对象，该函数会自动处理特殊字符的转义，确保生成合法的JSON结构。

修改后的触发器函数如下：

BEGIN;

ALTER TABLE mytable1 ADD COLUMN subaccnt TEXT REFERENCES subaccnt(uniq_id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE OR REPLACE FUNCTION enforce_mytable1_unused() RETURNS trigger AS $$
DECLARE
    servicesJson jsonb;
BEGIN
    servicesJson := jsonb_build_object(OLD.name, jsonb_build_object('enabled', '1'));
    IF EXISTS (SELECT 1 FROM mytable2 WHERE services @> servicesJson) THEN
        RAISE EXCEPTION 'Removing referenced monitoring services is not allowed: %', OLD.name;
    END IF;
    RETURN OLD;
END
$$  LANGUAGE plpgsql;
COMMIT;

额外说明 #

• 原记录中存在不同数量的反斜杠（如New\protection\plan、Australia\\protection\\plan），这是插入时转义处理不一致导致的，但使用jsonb_build_object后，无论原字段反斜杠数量如何，都会被自动转换为JSON合法格式。
• 该修改同时适用于更新名称字段的场景，确保所有涉及JSON构造的操作都符合语法规范。

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