PostgreSQL中UPDATE子查询结果修正及姓名去数字更新方案咨询

首先创建测试表并插入数据：

create table table1 (FirstName varchar(255), LastName varchar(255));

INSERT INTO table1 (FirstName, LastName) VALUES ('Mariah1', 'Billy3');
INSERT INTO table1 (FirstName, LastName) VALUES ('Mo2', 'Molly2');
INSERT INTO table1 (FirstName, LastName) VALUES ('Sally3', 'Silly1');

需求是批量去除姓名末尾的数字，尝试执行以下SQL后，结果出现带{}的数组格式（如{Mariah}），添加[0]操作无效：

UPDATE table1 t
SET (FirstName, LastName) = (
  select
  regexp_matches(FirstName ,'(\w+)\d+') as updatedFirstName,
  regexp_matches(LastName ,'(\w+)\d+') as updatedLastName
  FROM table1 u
  WHERE u.FirstName = t.FirstName and u.LastName = t.LastName
)

咨询两个问题：

1. 如何将单行子查询结果转为('abc','def')形式的简单元组以修正问题；
2. PostgreSQL环境下实现该批量更新的替代方案。

1. 修正原查询：数组转字符串元组 #

regexp_matches返回的是文本数组类型，直接赋值会保留数组结构。要提取捕获到的字符串，需用(regexp_matches(...))[1]获取数组第一个元素（对应正则的第一个捕获组）。同时优化关联条件（用ctid避免姓名重复时匹配错误），修正后的SQL：

UPDATE table1 t
SET (FirstName, LastName) = (
  SELECT
    (regexp_matches(u.FirstName, '(\w+)\d+'))[1],
    (regexp_matches(u.LastName, '(\w+)\d+'))[1]
  FROM table1 u
  WHERE u.ctid = t.ctid
);

更简洁的写法（无需子查询）：

UPDATE table1
SET 
  FirstName = (regexp_matches(FirstName, '(\w+)\d+'))[1],
  LastName = (regexp_matches(LastName, '(\w+)\d+'))[1];

注：如果姓名可能不含末尾数字，需用COALESCE((regexp_matches(...))[1], FirstName)避免更新为NULL。

2. 批量更新替代方案 #

方案一：用regexp_replace直接替换数字 #

最直观高效的方式，直接将末尾数字替换为空字符串：

UPDATE table1
SET 
  FirstName = regexp_replace(FirstName, '\d+$', ''),
  LastName = regexp_replace(LastName, '\d+$', '');

• \d+$匹配字符串末尾的1个或多个数字，替换后直接得到纯文本姓名；
• 若姓名无数字，会保留原内容（不会变为NULL）。

方案二：用substring提取目标内容 #

利用PostgreSQL的substring正则捕获功能，直接返回捕获到的字符串：

UPDATE table1
SET 
  FirstName = substring(FirstName from '^(\w+)\d+$'),
  LastName = substring(LastName from '^(\w+)\d+$');

• ^(\w+)\d+$精确匹配"字母数字序列+末尾数字"的格式，提取前面的字母数字部分；
• 若需要兼容无数字的姓名，可改为substring(FirstName from '(\w+)')，提取第一个连续字母数字序列。

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