PostgreSQL软删除触发器时间戳比较条件未触发问题

核心原因 #

PostgreSQL 中，NULL 和任何值使用 != 或 = 比较的结果都是 NULL，而 IF 条件会将 NULL 判定为逻辑假。你的场景里 OLD.deleted_at 是 NULL，NEW.deleted_at 是有效时间戳，NEW.deleted_at != OLD.deleted_at 的结果是 NULL，导致整个 NEW.deleted_at IS NOT NULL AND NEW.deleted_at != OLD.deleted_at 条件不成立。

解决方法 #

方法1：使用 IS DISTINCT FROM 运算符 #

PostgreSQL 提供的 IS DISTINCT FROM 会将 NULL 视为普通有效值处理，NULL 和非 NULL 值比较会返回真。修改条件为：

IF NEW.deleted_at IS NOT NULL AND NEW.deleted_at IS DISTINCT FROM OLD.deleted_at THEN

方法2：显式处理 NULL 场景 #

如果不想用专用运算符，可以拆分条件，覆盖 OLD 为 NULL 的情况：

IF (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL) 
   OR (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NOT NULL AND NEW.deleted_at != OLD.deleted_at) THEN

这种写法逻辑等价，但不如 IS DISTINCT FROM 简洁。

额外优化建议 #

1. 触发器触发条件优化：在创建触发器时，直接指定仅当 deleted_at 字段变更时触发，减少不必要的执行：

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_a
FOR EACH ROW
WHEN (OLD.deleted_at IS DISTINCT FROM NEW.deleted_at)
EXECUTE FUNCTION your_trigger_function();

2. 日志验证：可以在触发器函数中添加日志输出，直观确认值与比较结果：

RAISE NOTICE 'OLD.deleted_at: %, NEW.deleted_at: %', OLD.deleted_at, NEW.deleted_at;
RAISE NOTICE 'Comparison result (NEW != OLD): %', NEW.deleted_at != OLD.deleted_at;

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