PostgreSQL多表数组追加/拼接问题技术求助

正确SQL语句 #

要实现将artists表中匹配艺术家的流派数组合并到event_data对应行的流派数组中，可以使用关联子查询结合数组展开与聚合的方式，彻底解决子查询返回多行的问题：

UPDATE event_data ed
SET genres = (
    -- 合并原流派与匹配艺术家的所有流派元素，可选去重
    SELECT array_agg(DISTINCT elem)
    FROM (
        -- 提取当前event_data行的原有流派元素
        SELECT unnest(ed.genres) AS elem
        UNION ALL
        -- 提取所有匹配艺术家的流派元素
        SELECT unnest(a.genres) AS elem
        FROM artists a
        WHERE a.artist_name = ANY(ed.artists)
    ) combined_genres
)
-- 仅对存在匹配艺术家的行执行更新，避免无效操作
WHERE EXISTS (
    SELECT 1 FROM artists a WHERE a.artist_name = ANY(ed.artists)
);

原代码问题分析 #

你之前尝试的SQL报错核心原因：

• 使用array_append时，子查询直接返回了多个数组，但array_append仅支持追加单个元素，无法处理多行结果
• 使用array_cat时，未对多个匹配艺术家的数组做聚合处理，子查询返回了多组数组，无法直接与原数组拼接

逻辑说明 #

1. 针对event_data的每一行，先展开原有genres数组的所有元素
2. 关联artists表，匹配该行artists数组中包含的所有艺术家，展开他们的genres数组元素
3. 合并两类元素后，通过array_agg重新聚合成数组，DISTINCT用于去除重复流派（不需要可直接移除）
4. 仅对存在匹配艺术家的行执行更新，避免无意义的空操作

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