PostgreSQL：如何在ON CONFLICT UPDATE中使用CTE的值？报错解决

问题场景 #

我有一张名为legacy_hash的表，包含_key (string)和data (json)两列，执行批量upsert（插入或更新）操作时遇到报错：

missing FROM-clause entry for table "updates"

如果将DO UPDATE SET替换为DO NOTHING，报错就会消失。我尝试过在UPDATE部分使用EXCLUDED.field和EXCLUDED.value，但又触发了新的报错：

column excluded.field does not exist

我的需求是：当_key冲突时，更新data字段里的JSON对象，将对应字段的值设置为旧值+新值。当前有问题的批量查询语句如下：

WITH updates AS 
(
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
)
INSERT INTO "legacy_hash" ("_key", "data")
SELECT _key, jsonb_build_object(field, value) FROM updates
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[updates.field], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>updates.field)::NUMERIC, 0) + updates.value)
)

另外，我有一个适用于单条(key, field, value)数据的可用版本：

INSERT INTO "legacy_hash" ("_key", "data")
VALUES ($1::TEXT, jsonb_build_object($2::TEXT, $3::NUMERIC))
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[$2::TEXT], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>$2::TEXT)::NUMERIC, 0) + $3::NUMERIC)
)

报错原因 #

1. 第一个报错是因为在ON CONFLICT DO UPDATE块中，默认只能访问目标表（legacy_hash）和EXCLUDED虚拟表（存储待插入的冲突数据），无法直接引用CTE里的updates表，必须显式关联。
2. 第二个报错是因为EXCLUDED对象只包含插入语句中目标表的列（也就是_key和data），没有单独的field和value字段——毕竟插入的是构造好的JSON对象，EXCLUDED.data是整个JSON，不是拆分后的字段值。

修复后的批量查询 #

基础修复版本 #

通过在DO UPDATE后添加FROM子句关联CTE的updates表，匹配_key后就能正常引用field和value：

WITH updates AS 
(
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
)
INSERT INTO "legacy_hash" ("_key", "data")
SELECT _key, jsonb_build_object(field, value) FROM updates
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[u.field], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>u.field)::NUMERIC, 0) + u.value)
)
FROM updates u
WHERE u._key = "legacy_hash"._key;

优化版本（处理重复key/field） #

如果批量数据中存在同一个_key对应多个field，或者同一个_key+field组合多次出现的情况，建议先对更新数据聚合求和，避免重复更新导致的覆盖问题：

WITH updates AS 
(
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
),
aggregated_updates AS (
    SELECT _key, field, SUM(value) AS value
    FROM updates
    GROUP BY _key, field
)
INSERT INTO "legacy_hash" ("_key", "data")
SELECT _key, jsonb_build_object(field, value) FROM aggregated_updates
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[u.field], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>u.field)::NUMERIC, 0) + u.value)
)
FROM aggregated_updates u
WHERE u._key = "legacy_hash"._key;

内容的提问来源于stack exchange，提问作者Barış Uşaklı