PostgreSQL 16：如何从字符串解析成分更新产品表？

问题场景 #

现有两张业务表：

1. import表：存储产品的非结构化成分描述，material字段格式为前缀: <百分比>% <成分名>, ...（示例：Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane）
2. toode表：存储产品的结构化成分数据，包含mprots1-3（成分百分比字段）和materjal1-5（成分名称字段，需截取前6位）

需要将import表中的非结构化成分数据解析后，更新到toode表对应产品的字段中。

表结构与示例数据 #

import表

create table import 
(
    articleNumber char(20) primary key, 
    material char(70) 
);

insert into import 
values ('TEST', 'Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane' );

toode表

create table toode 
(
    originart char(20) primary key,
    mprots1 Numeric(9,3),
    mprots2 Numeric(9,3),
    mprots3 Numeric(9,3),
    materjal1 char(6),
    materjal2 char(6),
    materjal3 char(6),
    materjal4 char(6),
    materjal5 char(6) 
);

insert into toode (originart)  
values ('TEST');

目标更新结果 #

mprots1 = 50
mprots2 = 49
mprots3 = 1
materjal1 = Organi
materjal2 = Cotton
materjal3 = Elasta

实现SQL语句 #

使用PostgreSQL的正则匹配、行转列技巧，通过UPDATE结合子查询完成解析与更新：

UPDATE toode t
SET
    mprots1 = parsed.p1,
    mprots2 = parsed.p2,
    mprots3 = parsed.p3,
    materjal1 = parsed.m1,
    materjal2 = parsed.m2,
    materjal3 = parsed.m3
FROM (
    SELECT
        i.articleNumber,
        -- 提取第1-3个成分的百分比
        MAX(CASE WHEN rn = 1 THEN (regexp_match(item, '(\d+)%')[1])::numeric END) AS p1,
        MAX(CASE WHEN rn = 2 THEN (regexp_match(item, '(\d+)%')[1])::numeric END) AS p2,
        MAX(CASE WHEN rn = 3 THEN (regexp_match(item, '(\d+)%')[1])::numeric END) AS p3,
        -- 提取第1-3个成分的名称并截取前6位
        MAX(CASE WHEN rn = 1 THEN left(regexp_replace(item, '\d+% ', ''), 6) END) AS m1,
        MAX(CASE WHEN rn = 2 THEN left(regexp_replace(item, '\d+% ', ''), 6) END) AS m2,
        MAX(CASE WHEN rn = 3 THEN left(regexp_replace(item, '\d+% ', ''), 6) END) AS m3
    FROM import i
    -- 清理前缀后拆分每个成分条目
    CROSS JOIN regexp_split_to_table(
        regexp_replace(i.material, '^.+: ', '', 'g'),
        ', '
    ) AS item
    -- 给成分分配行号，匹配toode的字段顺序
    LEFT JOIN LATERAL (
        SELECT row_number() OVER (ORDER BY item) AS rn
    ) AS rn_seq ON true
    GROUP BY i.articleNumber
) parsed
WHERE t.originart = parsed.articleNumber;

代码说明 #

1. 前缀清理：用regexp_replace(i.material, '^.+: ', '', 'g')移除material字段开头的描述前缀（如Main fabric: ）
2. 成分拆分：通过regexp_split_to_table将清理后的字符串按, 分割为单个成分条目
3. 行号分配：利用row_number()给每个成分排序，确保第1个成分对应mprots1/materjal1，以此类推
4. 字段提取：
   • 百分比：用regexp_match(item, '(\d+)%')[1]提取数字部分并转为数值类型
   • 成分名称：用regexp_replace(item, '\d+% ', '')移除百分比标识，再用left(...,6)截取前6位
5. 行转列：通过CASE WHEN结合MAX聚合函数，将多行成分数据转为对应列，匹配toode表的字段结构
6. 关联更新：将解析结果与toode表通过产品编号关联，完成批量更新

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