PostgreSQL拆分address字符串提取数值并生成指定多列

需求说明 #

现有PostgreSQL表中address字段格式示例：

Tow 10 Floor 223, Ward AA BB, District CC DD, City E F
Tow 110 Floor 23, Ward BB AA, District DD CC, City F E

需提取Tow对应数值、Floor对应数值、Ward对应内容，去除第二个逗号后的部分，并将结果作为新列插入表中。

实现步骤 #

• 添加新列：先为目标表新增三个字段

ALTER TABLE your_table_name
ADD COLUMN tow INTEGER,
ADD COLUMN floor INTEGER,
ADD COLUMN ward TEXT;

（替换your_table_name为实际表名）

• 更新新列数据：通过字符串函数提取对应内容并写入新列

UPDATE your_table_name
SET
  tow = (regexp_match(address, 'Tow (\d+)'))[1]::INTEGER,
  floor = (regexp_match(address, 'Floor (\d+)'))[1]::INTEGER,
  ward = trim(split_part(split_part(address, ',', 2), 'Ward '));

逻辑说明 #

• regexp_match(address, 'Tow (\d+)')：匹配Tow后的数字片段，取第一个捕获组转换为整数得到tow值
• regexp_match(address, 'Floor (\d+)')：同理匹配Floor后的数字片段得到floor值
• split_part(address, ',', 2)：按逗号拆分字符串取第二部分，再通过trim(split_part(..., 'Ward '))去除Ward 前缀并清理首尾空格，得到ward内容

性能提示 #

数据量超10000行时，该语句执行效率稳定；若表涉及业务读写，可考虑分批更新避免长时间锁表。

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