PostgreSQL触发器防NULL覆盖时更新部分列报NEW字段不存在如何解决

问题根因 #

你遇到的报错是因为在触发器函数中硬编码访问了所有字段，当更新操作仅涉及部分列时，未被包含在更新范围内的字段不会出现在NEW记录的上下文中，直接访问就会抛出字段不存在的错误。

最优方案：直接在INSERT ... ON CONFLICT语句中实现逻辑，无需触发器 #

完全不需要额外写触发器，在更新时用COALESCE判断即可，性能更好也更易维护：

INSERT INTO mytable(x, y, z, company, first_name, 其他需要插入的字段)
VALUES (val_x, val_y, val_z, val_company, val_first_name, 对应字段的值)
ON CONFLICT(x) DO UPDATE SET
  company = COALESCE(EXCLUDED.company, mytable.company),
  first_name = COALESCE(EXCLUDED.first_name, mytable.first_name),
  -- 其他字段都按照该规则编写即可
  ...;

说明：EXCLUDED指代你原本要插入的那行数据，上述逻辑实现的效果是：如果待更新的新值非NULL就用新值，否则保留表中原有值，完全符合你的业务需求。

方案二：改造触发器适配部分列更新场景 #

如果你必须用触发器实现统一逻辑，可以通过jsonb类型的字段存在性检查来避免访问不存在的字段，改造后的触发器函数如下：

CREATE OR REPLACE FUNCTION public.clean_update()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    new_json jsonb := to_jsonb(NEW);
BEGIN
    -- 逐个判断字段是否存在于NEW中，存在才做COALESCE处理
    IF new_json ? 'company' THEN
        NEW.company = COALESCE(NEW.company, OLD.company);
    END IF;
    IF new_json ? 'first_name' THEN
        NEW.first_name = COALESCE(NEW.first_name, OLD.first_name);
    END IF;
    IF new_json ? 'last_name' THEN
        NEW.last_name = COALESCE(NEW.last_name, OLD.last_name);
    END IF;
    IF new_json ? 'address' THEN
        NEW.address = COALESCE(NEW.address, OLD.address);
    END IF;
    IF new_json ? 'country' THEN
        NEW.country = COALESCE(NEW.country, OLD.country);
    END IF;
    IF new_json ? 'phone' THEN
        NEW.phone = COALESCE(NEW.phone, OLD.phone);
    END IF;
    IF new_json ? 'mail' THEN
        NEW.mail = COALESCE(NEW.mail, OLD.mail);
    END IF;
    IF new_json ? 'evaluation' THEN
        NEW.evaluation = COALESCE(NEW.evaluation, OLD.evaluation);
    END IF;
    IF new_json ? 'positive' THEN
        NEW.positive = COALESCE(NEW.positive, OLD.positive);
    END IF;
    IF new_json ? 'neutral' THEN
        NEW.neutral = COALESCE(NEW.neutral, OLD.neutral);
    END IF;
    IF new_json ? 'negative' THEN
        NEW.negative = COALESCE(NEW.negative, OLD.negative);
    END IF;
    IF new_json ? 'nb_products' THEN
        NEW.nb_products = COALESCE(NEW.nb_products, OLD.nb_products);
    END IF;
    IF new_json ? 'nb_sales' THEN
        NEW.nb_sales = COALESCE(NEW.nb_sales, OLD.nb_sales);
    END IF;
    IF new_json ? 'nb_sale_url' THEN
        NEW.nb_sale_url = COALESCE(NEW.nb_sale_url, OLD.nb_sale_url);
    END IF;
    IF new_json ? 'unregistered' THEN
        NEW.unregistered = COALESCE(NEW.unregistered, OLD.unregistered);
    END IF;
    RETURN NEW;
END;
$BODY$;

说明：to_jsonb(NEW) ? '字段名' 语法可以准确判断NEW记录中是否包含指定字段，无论字段值是否为NULL，这样就不会出现字段不存在的报错。

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