PostgreSQL中如何简便实现逗号分隔列值拆分单行存储

针对你需要将列内逗号分隔的数值拆分为多行的需求，PostgreSQL可以通过内置函数快速实现，无需手动插入每一条数据：

方法1：直接查询拆分结果 #

假设你的原表名为original_table，执行以下SQL即可得到目标格式的数据：

SELECT
    column_a,
    -- 拆分逗号+任意空格分隔的字符串为数组，再展开为多行，转成整数类型
    unnest(regexp_split_to_array(trim(column_b), ',\s*'))::int AS column_b
FROM original_table
-- 可选：过滤空值或空字符串的行
WHERE column_b IS NOT NULL AND column_b != '';

方法2：直接插入到新表 #

如果要把拆分结果直接存入你创建的pp表，可以用INSERT ... SELECT语句一步完成：

CREATE TABLE pp (
    id int,
    toppings int
);

INSERT INTO pp (id, toppings)
SELECT
    column_a,
    unnest(regexp_split_to_array(trim(column_b), ',\s*'))::int
FROM original_table
WHERE column_b IS NOT NULL AND column_b != '';

关键函数说明 #

• regexp_split_to_array(column_b, ',\s*')：用正则表达式匹配逗号加任意数量的空格，将column_b的字符串拆分为字符串数组
• unnest()：将数组的每个元素转换为单独的一行
• ::int：将拆分后的字符串转换为整数类型，确保数据类型匹配

内容的提问来源于stack exchange，提问作者K.J.K