PostgreSQL提取子串并批量替换指定行字段值的通用方案

需求回顾 #

针对目标表，需按id分组处理：

• 从每组内fid=901的val字段中提取8位日期（如示例中的20220615）
• 将同组内fid=4055和fid=4057的val字段替换为该日期值

通用解决方案 #

采用CTE（公共表表达式）先批量获取每个id对应的目标日期，再执行批量更新，适配大量id的处理场景：

WITH target_dates AS (
    SELECT 
        id,
        -- 提取val中的8位日期数字
        (regexp_match(val, '\d{8}'))[1] AS target_val
    FROM your_table_name
    WHERE fid = 901
)
UPDATE your_table_name t
SET val = td.target_val
FROM target_dates td
WHERE t.id = td.id
AND t.fid IN (4055, 4057);

代码说明 #

1. CTE部分：target_dates查询为每个id提取出fid=901行中val字段里的8位日期，存储为target_val
2. 更新部分：关联主表与CTE，匹配相同id，仅更新fid为4055或4057的行，将val替换为对应目标日期

验证结果 #

执行上述SQL后，目标表数据会更新为预期格式：

id       fid                 ts             val
403307394   900     2021-09-20 00:00:00.000   Futures
403307394   901     2021-09-20 00:00:00.000   FSR3 SI 20220615 CS
403307394   4055    2021-09-20 00:00:00.000   20220615
403307394   4057    2021-09-20 00:00:00.000   20220615

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