PostgreSQL中UPDATE为何未限制在CTE指定的行范围内？

问题场景 #

环境：PostgreSQL 13.10
通过REST API生成的CSV更新SQL模板如下，本意是更新CTE中JSON数据指定的行，但实际执行后会更新表中所有行——即使移除视图直接操作原表，问题依然存在：

WITH cte AS (SELECT '[...CSV数据编码为JSON...]'::json AS data)
UPDATE t SET c1 = _.c1, c2 = _.c2, ...
FROM (SELECT * FROM JSON_POPULATE_RECORDSET(NULL::t, (SELECT data FROM cte))) _;

测试验证过程：

-- 创建测试表和10万条数据
create table t (tid int, tval text);
insert into t (tid, tval) select generate_series(1,100000), md5(random()::text);
-- 创建视图，重命名列名
create view v as select tid as id, tval as val from t;
-- 确认初始数据量
select count(*) from v;
-- 验证CTE生成的目标数据，确实只有2行
WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte)) _;
-- 执行UPDATE，结果更新了100000行（全表）
begin;
WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
UPDATE v SET val = _.val, id = _.id
FROM (SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte))) _;
-- 确认所有行都被更新为test开头的值
select count(*) from v where val like 'test%';

问题原因 #

核心问题是UPDATE语句缺少关联条件：当UPDATE使用FROM子句时，如果没有明确指定目标表（或视图）与FROM子句中数据集的匹配关系，PostgreSQL会将两者做笛卡尔积关联——也就是目标表的每一行都会和FROM里的每一行匹配，最终所有行都被更新（如果FROM有多个行，通常是最后一行的值覆盖所有）。

解决方案 #

在UPDATE语句中添加WHERE子句，用唯一标识列（比如示例中的id/tid）关联目标对象和FROM子句的数据集，确保只有匹配的行才会被更新。

修正后的操作视图的SQL #

begin;
WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
UPDATE v 
SET val = _.val, id = _.id
FROM (SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte))) _
WHERE v.id = _.id; -- 关键：添加关联条件
-- 验证结果，只有2行被更新
select count(*) from v where val like 'test%';
commit;

修正后的操作原表的SQL #

begin;
WITH cte as (SELECT '[{"tid":99991,"tval":"test3"},{"tid":99992,"tval":"test4"}]'::json AS data)
UPDATE t 
SET tval = _.tval
FROM (SELECT * FROM json_populate_recordset (NULL::t, (SELECT data FROM cte))) _
WHERE t.tid = _.tid; -- 关联主键列
select count(*) from t where tval like 'test%';
commit;

验证结果 #

执行修正后的SQL后，count(*)返回结果为2，说明只有指定的两行被更新，全表更新的问题解决。

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