PostgreSQL单列多正则替换实现方案咨询

错误原因分析 #

你触发的more than one row returned by a subquery used as an expression错误，是因为嵌套子查询(select regexp_replace(...) from work_table wt)会返回表中所有行的处理结果，而外层regexp_replace只能接收单个值，导致冲突。根本不需要用子查询，直接对单行列值嵌套替换函数即可。

简洁解决方案 #

方案1：嵌套regexp_replace（保留正则精准匹配）

如果你需要用正则确保匹配的精准性（比如仅替换末尾的obl、开头/结尾的Rep），直接嵌套regexp_replace函数即可，无需子查询：

SELECT 
  regexp_replace(
    regexp_replace(wt.region_with_type, '\sobl$', 'oblast'),
    '^Rep\s|\sRep$', 'Republic'
  ) AS project_f_region
FROM work_table wt;

方案2：嵌套replace（更高效的固定子串替换）

如果你的替换规则不需要正则匹配（比如确认obl仅出现在末尾且前带空格，Rep仅在开头后带空格或结尾前带空格），用replace函数性能更优：

SELECT 
  replace(
    replace(
      replace(wt.region_with_type, ' obl', ' oblast'),
      'Rep ', 'Republic '
    ),
    ' Rep', ' Republic'
  ) AS project_f_region
FROM work_table wt;

方案3：单正则多模式替换（更简洁的写法）

通过一个正则匹配所有需要替换的模式，结合分组判断完成批量替换：

SELECT 
  regexp_replace(
    wt.region_with_type,
    '^(Rep)\s|(\sRep)$|(\sobl)$',
    CASE
      WHEN substring(wt.region_with_type from '^(Rep)\s') IS NOT NULL THEN 'Republic '
      WHEN substring(wt.region_with_type from '(\sRep)$') IS NOT NULL THEN ' Republic'
      WHEN substring(wt.region_with_type from '(\sobl)$') IS NOT NULL THEN ' oblast'
    END,
    'g'
  ) AS project_f_region
FROM work_table wt;

这些方法都不需要CTE，保持查询简洁且彻底避免了子查询返回多行的问题。

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