PostgreSQL：基于Temp表替换Book表Varchar数组指定元素的脚本需求

现有两张PostgreSQL表，结构如下：

• Book表：包含字段 Id(varchar(40))、Name(varchar(255))、Tags(varchar(40)[])
• Temp表：包含字段 Name(varchar(255))、Id(varchar(40))、duplicateids(varchar(40)[])

需要实现：遍历Book表的Tags数组，若数组中的元素存在于任意Temp表的duplicateids数组中，则将该元素替换为对应Temp表的Id值；无匹配的元素保持原样。

示例数据 #

Book表初始数据

Temp表数据

执行脚本后Book表预期结果

注：原示例中"testtag5"应为笔误，实际对应Temp1的Id是"testtag"

可以通过数组拆分-关联匹配-数组重组的思路实现，以下是完整的PostgreSQL更新脚本：

WITH updated_tags AS (
    SELECT
        b.Id,
        -- 对每个tag进行替换：匹配到则用Temp.Id，否则保留原tag
        ARRAY_AGG(COALESCE(t.Id, tag) ORDER BY idx) AS new_tags
    FROM
        Book b
        -- 拆分Book的Tags数组为单行，保留原索引以维持数组顺序
        CROSS JOIN UNNEST(b.Tags) WITH ORDINALITY AS u(tag, idx)
        -- 左关联Temp表，匹配tag是否在duplicateids数组中
        LEFT JOIN Temp t ON tag = ANY(t.duplicateids)
    GROUP BY
        b.Id
)
UPDATE Book b
SET Tags = ut.new_tags
FROM updated_tags ut
WHERE b.Id = ut.Id;

脚本逻辑说明 #

1. 拆分数组：使用UNNEST(b.Tags) WITH ORDINALITY将Book的Tags数组拆分为单行数据，同时保留每个元素的原始索引（idx），确保重组后的数组顺序和原数组一致。
2. 关联匹配：通过LEFT JOIN Temp t ON tag = ANY(t.duplicateids)找到每个tag对应的Temp表Id，无匹配时t.Id为NULL。
3. 替换与重组：用COALESCE(t.Id, tag)实现“匹配则替换，否则保留原tag”的逻辑，再通过ARRAY_AGG(...) ORDER BY idx将单行数据重新聚合成数组，维持原顺序。
4. 更新表数据：通过CTE（公共表达式）将处理后的新数组更新回Book表。

验证执行 #

执行上述脚本后，Book表的数据将和预期结果一致，完成Tags数组的批量替换。

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