Python与Postgres正则差异及迁移方法

IT行业相对于一般传统行业，发展更新速度更快，一旦停止了学习，很快就会被行业所淘汰。所以我们需要踏踏实实的不断学习，精进自己的技术，尤其是初学者。今天golang学习网给大家整理了《Python 与 PostgreSQL 正则差异及迁移攻略》，聊聊，我们一起来看看吧！

Python 的 `re` 模块与 PostgreSQL 的 POSIX 正则引擎在元字符、修饰符位置、边界断言等方面存在显著差异，直接复用会导致语法错误或语义偏差；本文详解关键区别，并提供可直接用于 PostgreSQL 的等效写法。

正则表达式并非“一次编写，处处运行”的标准——不同系统采用的正则引擎（如 Python 的 PCRE-like re、PostgreSQL 的 POSIX ERE/ARE、JavaScript 的 ECMAScript、Java 的 java.util.regex）在语法支持、语义定义和行为细节上各不相同。你遇到的错误：

SELECT (regexp_matches('Jl. ABC No.01', '^(.*?)(?i)\b no\b'))[1];
-- ERROR: invalid regular expression: quantifier operand invalid

正是典型体现：PostgreSQL 不支持内联修饰符 (?i) 出现在模式中间（如 (?i)\b no\b），且其 \b 并非“单词边界”，而是字面意义的退格字符（backspace） —— 这与 Python 中 \b 表示 word boundary 完全不同。

✅ 正确的 PostgreSQL 等效写法

PostgreSQL 使用 ARE（Advanced Regular Expressions），要求所有嵌入选项（如大小写不敏感）必须置于正则表达式开头，且使用 \y（word boundary）、\m（word start）、\M（word end）替代 \b：

⚠️ 注意：PostgreSQL 的 regexp_matches() 返回 text[] 数组，索引从 1 开始；若要提取捕获组，推荐使用更直观的 REGEXP_REPLACE 或 SUBSTRING(... FROM ...)。

✅ PostgreSQL 实战：拆分 street 和 house_number

假设表 addresses 含列 address TEXT，目标新增 street 和 house_number：

-- 添加列（可选）
ALTER TABLE addresses 
  ADD COLUMN street TEXT,
  ADD COLUMN house_number TEXT;

-- 填充 street：提取 "No" 之前的部分（不包含 "No" 及其前导空格）
UPDATE addresses 
SET street = TRIM(
  SUBSTRING(address FROM '(?i)^(.*?)\y no \y')
);

-- 填充 house_number：提取 "No" 后紧跟的编号（支持 "No.01", "NO 12B" 等）
UPDATE addresses 
SET house_number = TRIM(
  SUBSTRING(address FROM '(?i)\y no \.? \s* ([\dA-Z]+)')
);

✅ 示例验证：

SELECT 
  address,
  SUBSTRING('Jl. ABC No.01' FROM '(?i)^(.*?)\y no \y') AS street,
  SUBSTRING('Jl. ABC No.01' FROM '(?i)\y no \.? \s* ([\dA-Z]+)') AS house_number;
-- 结果：street = 'Jl. ABC', house_number = '01'

? 关键差异速查表

✅ 总结

• ❌ 不要将 Python 正则原样复制到 PostgreSQL；
• ✅ 所有修饰符（(?i)、(?m) 等）必须置于正则最前端；
• ✅ 将 \b 全部替换为 \y（单词边界），避免误匹配退格符；
• ✅ 使用 SUBSTRING(col FROM 'regex') 替代 regexp_matches() 提取单个捕获组，更简洁可靠；
• ✅ 在生产环境上线前，务必用 SELECT SUBSTRING(...) 对典型地址样本做充分测试。

掌握这些核心差异，即可高效、安全地将地址解析逻辑从 Pandas 迁移至数据库层，提升 ETL 性能与一致性。

今天关于《Python与Postgres正则差异及迁移方法》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！