PostgreSQL如何根据字段内容指定查询结果的目标列？

问题背景 #

现有PostgreSQL表数据如下：

需要通过SQL查询，根据Value字段下划线前的内容匹配目标列，将下划线后的数值填充到对应列，得到如下结果：

目前已能通过以下SQL提取下划线前的类型部分：

SELECT
left("Value", strpos("Value", '_') - 1) as "Type"

但不清楚如何根据提取的类型确定要填充的目标列，寻求可行的SQL实现方式。

解决方法 #

可以用PostgreSQL的CASE条件语句结合字符串拆分函数来实现，针对每个目标列做匹配判断，匹配则填充对应数值，否则返回null。

方法一：结合strpos与substring #

SELECT
    "Id",
    -- 填充User列：判断类型为User时提取下划线后的数值
    CASE WHEN left("Value", strpos("Value", '_') - 1) = 'User'
         THEN substring("Value" from strpos("Value", '_') + 1)::int
         ELSE null END AS "User",
    -- 填充Region列：判断类型为Region时提取下划线后的数值
    CASE WHEN left("Value", strpos("Value", '_') - 1) = 'Region'
         THEN substring("Value" from strpos("Value", '_') + 1)::int
         ELSE null END AS "Region"
FROM your_table_name;

方法二：用split_part简化写法 #

split_part可以直接按分隔符拆分字符串，避免多次调用strpos，代码更简洁：

SELECT
    "Id",
    CASE WHEN split_part("Value", '_', 1) = 'User'
         THEN split_part("Value", '_', 2)::int
         ELSE null END AS "User",
    CASE WHEN split_part("Value", '_', 1) = 'Region'
         THEN split_part("Value", '_', 2)::int
         ELSE null END AS "Region"
FROM your_table_name;

关键说明 #

• split_part("Value", '_', 1)：按下划线拆分Value，取第一部分作为类型标识
• split_part("Value", '_', 2)：取拆分后的第二部分，也就是需要填充的数值，::int是将其转为整数类型（如果不需要整数可以去掉）
• 每个目标列通过CASE判断当前行的类型是否匹配，匹配则填充数值，否则返回null

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