PostgreSQL中拆分config列并基于键值对生成对应新列的方法

这问题我熟，PostgreSQL里处理这种键值对拆分转列的需求，有几种实用的方法，我给你详细说说：

方法一：利用JSONB类型转换（无需额外扩展） #

这种方法不用装额外插件，直接用PostgreSQL原生的JSON功能就能搞定，步骤是把config列的字符串转换成JSONB对象，再提取对应字段：

SELECT 
    company,
    config_json->>'User' AS "User",
    config_json->>'Key' AS "Key",
    config_json->>'Role' AS "Role",
    config_json->>'Location' AS "Location",
    config_json->>'Status' AS "Status"
FROM (
    SELECT 
        company,
        -- 把逗号分隔的键值对转换成合法的JSON字符串，再转成JSONB
        ('{"' || replace(replace(config, ':', '":"'), ',', '","') || '"}')::jsonb AS config_json
    FROM your_table_name -- 替换成你的实际表名
) AS sub;

解释：

• 内层子查询里的replace操作是把原字符串格式转换为JSON结构：
  1. 先把所有:替换成":"，比如User:xxx变成User":"xxx
  2. 再把所有,替换成","，比如User":"xxx,Key:ABC变成User":"xxx","Key":"ABC
  3. 前后加上{"和"}，就得到了合法的JSON字符串：{"User":"xxx","Key":"ABC"}
• 外层用->>操作符从JSONB对象里提取对应键的值，不存在的键会返回NULL，正好对应你期望输出里的空单元格。

方法二：使用HStore扩展（适合纯键值对场景） #

如果你经常处理这类键值对数据，可以启用PostgreSQL的hstore扩展，操作会更简洁：

首先需要先启用扩展（只需要执行一次）：

CREATE EXTENSION IF NOT EXISTS hstore;

然后执行查询：

SELECT 
    company,
    config_hstore -> 'User' AS "User",
    config_hstore -> 'Key' AS "Key",
    config_hstore -> 'Role' AS "Role",
    config_hstore -> 'Location' AS "Location",
    config_hstore -> 'Status' AS "Status"
FROM (
    SELECT 
        company,
        -- 把冒号替换成hstore要求的=>格式，再转成hstore类型
        replace(config, ':', '=>')::hstore AS config_hstore
    FROM your_table_name -- 替换成你的实际表名
) AS sub;

注意事项 #

• 两种方法都假设你的config列数据格式是规范的：每个键值对都是Key:Value格式，用逗号分隔，且键和值里不包含冒号或逗号（如果有特殊字符的话，需要额外处理转义）。
• 如果需要把结果持久化，可以把查询改成CREATE TABLE new_table AS ...来生成新表。

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