PostgreSQL跨表更新：仅更新需修改值含NULL的异常排查

需要从PostgreSQL的另一张表更新目标表，要求仅更新实际需要修改的值（已匹配正确的值不覆盖，确保更新行数统计准确），同时要处理字段为NULL的场景。

尝试常规UPDATE语句：

update table1 t1
set val = t2.val
from table2 t2
where t1.key = t2.key

添加and t1.val != t2.val可以处理非NULL值的差异，但无法更新NULL值（因为NULL和任何值的比较结果都是NULL，不满足条件）。为了处理NULL值，添加OR条件后写成：

update table1 t1
set val = t2.val
from table2 t2
where t1.key = t2.key
and t1.val != t2.val
OR t1.val is NULL AND t1.key IS NOT NULL;

执行后出现异常：第一次执行时，table1中id=2的val被错误更新为'x'，第二次执行才更新为正确的'y'。

使用CTE关联查询的UPDATE语句能得到正确结果，现疑问：为何简单JOIN写法会出现该异常？

附测试表结构与数据：

create table table1
(
    id   int,
    key  int,
    val varchar
);

create table table2
(
    key  int,
    val varchar
);

insert into table1 (id, key, val) values (1, null, null);
insert into table1 (id, key, val) values (2, 2, null);
insert into table1 (id, key, val) values (3, 3, 'z');

insert into table2 (key, val) values (1, 'x');
insert into table2 (key, val) values (2, 'y');
insert into table2 (key, val) values (3, 'z');

这个异常的核心原因有两个：

1. 逻辑运算符优先级错误：PostgreSQL中AND的优先级高于OR，所以你写的WHERE条件会被解析为：

   (t1.key = t2.key AND t1.val != t2.val) OR (t1.val IS NULL AND t1.key IS NOT NULL)
   

   对于table1中id=2的记录（key=2，val=NULL），它会匹配第二个OR分支的条件（t1.val IS NULL AND t1.key IS NOT NULL），此时没有t1.key = t2.key的限制，这条记录会和table2中的所有记录进行关联。
2. UPDATE FROM的多匹配处理规则：当一条目标记录匹配到FROM子句中的多条记录时，PostgreSQL会随机选择其中一条来执行更新操作。所以第一次执行时，可能恰好选中了table2中key=1（val='x'）的记录，导致错误更新；第二次执行时，该记录的val已经变为'x'，不再满足第二个OR分支的条件，只会匹配t1.key = t2.key AND t1.val != t2.val（此时t1.val='x'，t2.key=2的val='y'，满足差异条件），因此会更新为正确的'y'。

方法1：使用IS DISTINCT FROM处理NULL值比较 #

PostgreSQL提供了IS DISTINCT FROM运算符，它能正确处理NULL值的比较（当两个值一个为NULL另一个不为NULL，或者非NULL值不相等时，返回true），完美适配你的需求：

update table1 t1
set val = t2.val
from table2 t2
where t1.key = t2.key
and t1.val IS DISTINCT FROM t2.val;

这条语句会仅更新table1和table2中key匹配且val存在差异（包括NULL和非NULL的差异）的记录，不会出现多匹配问题。

方法2：使用CTE明确关联逻辑 #

如果偏好CTE写法，也可以通过CTE先筛选出需要更新的记录，再执行更新：

WITH to_update AS (
    SELECT t1.id, t2.val
    FROM table1 t1
    JOIN table2 t2 ON t1.key = t2.key
    WHERE t1.val IS DISTINCT FROM t2.val
)
UPDATE table1 t1
SET val = tu.val
FROM to_update tu
WHERE t1.id = tu.id;

内容的提问来源于stack exchange，提问作者Ori Haberman Browns