PostgreSQL中如何将多个字符串数组列合并为一个字符串数组

表结构与数据 #

现有如下表结构：

create table contacts (
    first_name varchar[],
    last_name varchar[]
);

插入两行测试数据：

INSERT INTO contacts (first_name, last_name)
VALUES (ARRAY['Samin'] , Array['Var']),
(ARRAY['Sara', 'pooya'] , Array['Rad', 'Mohammadi']);

select * from contacts;

查询需求 #

需要编写SQL查询，将每行中first_name和last_name数组中对应位置的元素用-拼接，最终返回格式如下：

#row1: {Samin-Var}
#row2: {Sara-Rad, pooya-Mohammadi}

解决方案 #

可以利用PostgreSQL的数组处理函数，结合分组聚合实现需求，具体SQL如下：

SELECT 
    '#' || row_number() OVER () || ': {' || array_to_string(agg_result, ', ') || '}' AS result
FROM (
    SELECT 
        array_agg(concat(fn, '-', ln)) AS agg_result
    FROM (
        SELECT 
            c.ctid,
            unnest(c.first_name) AS fn,
            unnest(c.last_name) AS ln
        FROM contacts c
    ) t
    GROUP BY ctid
) sub;

逻辑说明 #

1. 内层子查询通过unnest同时展开每行的两个数组，确保位置对应的元素一一配对；
2. 中间层按行（用ctid作为行唯一标识）分组，用array_agg将拼接后的fn-ln字符串重新聚合成数组；
3. 外层查询用array_to_string将数组转为字符串，再拼接成需求的格式。

如果只需要返回聚合后的数组（不带#rowX:前缀），可以简化为：

SELECT array_agg(concat(fn, '-', ln)) AS result
FROM (
    SELECT 
        unnest(first_name) AS fn,
        unnest(last_name) AS ln
    FROM contacts
) t
GROUP BY ctid;

内容的提问来源于stack exchange，提问作者Pooya Mohammadi Kazaj