如何将外键关联表的数据插入主表的数组列？

需求说明 #

将referencing表中pryomka_id与mainone表id匹配的image_path数据，批量追加到mainone表的image_pathes数组列中。

表结构回顾 #

主表mainone #

CREATE TABLE mainone (
    id bigint NOT NULL,
    task_id bigint NOT NULL,
    object_created_at character varying(191) NOT NULL,
    closed_at character varying(191) NOT NULL,
    object_name text NOT NULL,
    region_id integer NOT NULL,
    district_id integer NOT NULL,
    customer_inn bigint NOT NULL,
    customer_name character varying(191) NOT NULL,
    response json NOT NULL,
    image_pathes character varying(191) ARRAY,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);

关联表referencing #

CREATE TABLE referencing (
    id bigint NOT NULL,
    pryomka_id bigint NOT NULL,
    image_path character varying(191) NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);

数据迁移SQL #

核心更新语句 #

UPDATE mainone m
SET image_pathes = COALESCE(m.image_pathes, '{}'::varchar(191)[]) || r.image_paths_agg
FROM (
    SELECT 
        pryomka_id,
        ARRAY_AGG(DISTINCT image_path) AS image_paths_agg
    FROM referencing
    GROUP BY pryomka_id
) r
WHERE m.id = r.pryomka_id;

语句说明 #

1. 子查询按pryomka_id分组，聚合对应所有image_path为数组（DISTINCT用于避免重复路径）
2. COALESCE处理mainone.image_pathes为空的场景，默认使用空数组，避免拼接时产生NULL
3. 使用||操作符将原数组与新聚合数组拼接，实现追加效果

验证结果（执行更新前建议先验证） #

SELECT 
    m.id,
    m.image_pathes AS original_paths,
    r.image_paths_agg AS new_paths,
    COALESCE(m.image_pathes, '{}'::varchar(191)[]) || r.image_paths_agg AS final_paths
FROM mainone m
JOIN (
    SELECT 
        pryomka_id,
        ARRAY_AGG(DISTINCT image_path) AS image_paths_agg
    FROM referencing
    GROUP BY pryomka_id
) r ON m.id = r.pryomka_id;

可选调整 #

• 如果需要覆盖原数组而非追加，将更新语句中的COALESCE(m.image_pathes, '{}'::varchar(191)[]) || r.image_paths_agg替换为r.image_paths_agg即可
• 若不需要去重，移除子查询中的DISTINCT关键字

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