正则表达式，在 PostgreSQL 里平时大家用得少，可真到要用的时候全是问题。我见过不少人把在 Python 或 Perl 里跑得好好的正则，原封不动搬到 PostgreSQL 里就报错，或者匹配结果完全对不上；也见过有人在一张存了几十万条日志的表上直接跑正则筛选，一条 SQL 把数据库拖到半死。PostgreSQL 内置了非常完整的正则支持，从匹配、提取、替换到拆分都有对应函数，Python 里能做的操作，这里基本都能做。这篇东西不打算讲教科书式的语法清单，我把这几年在 PostgreSQL 里用正则处理线上数据时踩过的坑、沉淀下来的写法，按场景整理出来。内容偏实战，适合正在用 PostgreSQL 处理日志、清洗文本、做报表字段提取的工程师，也适合刚装上 PostgreSQL 16 想系统了解正则能力的同学。版本方面，我下面所有 SQL 都以 PostgreSQL 16 为准，部分新函数 16 之前没有，我会单独标注。

1. 先搞清楚 PostgreSQL 里的“正则”到底有几种姿势

PostgreSQL 给我的第一感觉是：正则能力强，但入口太多。同样是模糊匹配，你可以写LIKE、写SIMILAR TO、写~，还可以写regexp_like。新手很容易满脑子问号：这些到底有什么区别？我平时应该用哪个？

1.1 三种匹配工具定位完全不同

先说LIKE。它其实不算是正则表达式，只是简单模式匹配，只有两个通配符：%匹配任意长度字符串，_匹配单个字符。好处是简单、直观、快，坏处是表达力弱，比如想匹配“张”或“李”开头的人名，LIKE就得写两个条件拼OR。

SIMILAR TO是 SQL 标准里的折中方案，语法上混合了LIKE的通配符和正则的分支、字符类。比如SIMILAR TO '(张|李)%'能匹配张或李开头。但这个语法有点“四不像”，用的人很少，我在实际项目中基本没见过谁靠它做主逻辑。

真正强大的是第三类：POSIX 正则表达式，用~、~*这类操作符以及配套函数实现。这也是 PostgreSQL 区别于 MySQL 的一大优势。比如想匹配“张”或“李”开头，并且名字是两个字，直接写：

SELECT * FROM users WHERE name ~ '^[张李].';

从维护角度讲，团队里只要有人熟悉常见正则写法，~一段式就能解决复杂匹配，不需要东拼西凑多个LIKE。

1.2 为什么我默认用 POSIX 操作符

理由很简单：PostgreSQL 内置的正则引擎是高级正则表达式（ARE），它支持分支、分组、后向引用、非贪婪匹配、字符类这些现代正则基本功，已经覆盖了绝大多数业务场景。它和常见编程语言里的正则习惯非常接近，迁移成本低。

比如你有一条日志，想找出所有“ERROR 开头的编号”，Python 或者 Perl 里可能是ERROR ([A-Z0-9_]+)，在 PostgreSQL 里几乎不用改：

SELECT substring(log_line from 'ERROR ([A-Z0-9_]+)') AS error_code FROM app_logs;

我见过很多项目因为不知道 PostgreSQL 支持完整正则，硬生生用一串LIKE加OR实现，SQL 又臭又长，还容易漏匹配。规范做法就是：简单模糊查询继续用LIKE，复杂文本处理一律走 POSIX 正则。 如果你也像很多团队一样用 Docker 拉一个 PostgreSQL 实例做本地开发，下载哪个版本其实不用纠结，我建议直接上 16 或更新版本，旧库迁移也就一次pg_dump的事，换来的是更完整的正则函数族。

2. 匹配判断：用 ~、~*、!~ 批量清洗数据

正则最基础的使用场景就是判断某列是否符合某种格式，比如手机号、邮箱、身份证号。在 PostgreSQL 里，这类判断不需要专门写函数，直接用操作符就行。

2.1 四类操作符怎么选

PostgreSQL 提供了四个正则操作符：

注意~和LIKE的区别：~是完整的正则匹配，^和$这种锚点才起作用。LIKE里的%在~里可不会自动变成“任意长度”的意思，正则里写法是.*。

举个例子，我想找出所有包含连续六位以上数字的日志行：

SELECT log_line FROM app_logs WHERE log_line ~ '[0-9]{6,}';

再比如从用户表里筛出邮箱明显不合规的记录：

SELECT user_id, email FROM users WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

这条语句能直接当作数据质量检查脚本用，跑一遍把不合格的邮箱列表拉出来，再交给业务方核对。

2.2 现实场景中的校验写法

实际工作中，我常用“正则 + 开关字段”的方式做标记，而不是频繁更新主数据。比如在订单表里，售后备注有很多乱填的内容，想标记出那些可能包含电话号码的订单：

UPDATE orders SET needs_review = true WHERE remark ~ '1[3-9][0-9]{9}' AND needs_review = false;

加上AND needs_review = false的目的是避免每次跑重复更新。正则匹配本身消耗不小，能通过普通索引条件缩小范围就先缩小范围，等会讲性能时细说。

这里有个小技巧：如果你只是想判断“是否存在匹配”，用~操作符就够了，它返回布尔值。不需要把regexp_matches拉出来，那是提取场景才用的。很多人一上来就用regexp_matches，结果发现返回的是数组还得取值，白白增加复杂度。

3. 提取内容：regexp_matches 和 regexp_substr 的正确打开方式

匹配是“判断有没有”，提取是“把想要的部分拿出来”。PostgreSQL 里提取相关的函数有substring、regexp_matches、regexp_substr。用的场景不同，踩的坑也不一样。

3.1 踩过的坑：regexp_matches 的数组返回

regexp_matches是 PostgreSQL 一直就有的函数，但它有个非常容易坑人的地方：默认情况下，如果没有加g标志，它只返回第一个匹配结果；如果加了g，它返回所有匹配。但不管哪种情况，它返回的类型都是text[]，也就是一个数组。

看个例子，我想把'product123 price456'里所有数字都提出来：

SELECT regexp_matches('product123 price456', '[0-9]+', 'g');

输出是两行，每行一个数组：

{123} {456}

注意，不是直接输出123和456，而是{123}这种数组形式。如果你想要行内单个值，要用手去取数组第一项：

SELECT (regexp_matches('product123 price456', '[0-9]+', 'g'))[1];

这在处理日志里的错误码时很实用。例如：

SELECT log_id, (regexp_matches(log_line, 'ERROR: ([A-Z0-9_]+)', 'g'))[1] AS error_code FROM app_logs WHERE log_line ~ 'ERROR:';

用g标志时，一行日志可能出现多个错误码，这时会输出多行。如果你的业务逻辑只需要第一个，就别加g，然后在外层加LIMIT 1或者用array_agg合并。

3.2 PG16 新函数让提取更直观

从 PostgreSQL 16 开始，官方加入了一批更接近 Oracle 风格的正则函数，包括regexp_like、regexp_count、regexp_instr、regexp_substr。这几个函数对提取场景非常友好，最大的变化是返回值不再那么别扭。

比如提取日志里第一个错误码，旧写法是substring，PG16 可以直接用regexp_substr：

SELECT log_id, regexp_substr(log_line, 'ERROR: ([A-Z0-9_]+)') AS error_code FROM app_logs;

regexp_substr的重载参数还支持指定从第几个字符开始搜索、提取第几次出现的匹配。比如想提取第二次出现的数字：

SELECT regexp_substr('温度25℃，湿度16℃', '[0-9]+', 1, 2);

第二个参数是模式，第三个是起始位置，第四个是第几次出现。这个功能以前在 PostgreSQL 里写起来很痛苦，现在一句话搞定，强烈建议还在用老版本的朋友认真评估升级。

配合regexp_count，还能快速统计一段文本里某个模式出现的次数：

SELECT description, regexp_count(description, '[0-9]{4}-[0-9]{2}-[0-9]{2}') AS date_count FROM product_specs;

这个函数对做文本质量统计特别有用，以前要用regexp_matches加COUNT还得处理数组，现在直接出数。

4. 替换与重排：regexp_replace 的高阶用法

如果说提取是“读”，那替换就是“写”。regexp_replace是 PostgreSQL 里最常用的文本改写函数，比各种手动concat拼接字符串要优雅得多。

4.1 反向引用的威力

regexp_replace的强大之处在于替换串里可以引用匹配到的分组。比如把日期格式从2024-03-15改成15/03/2024：

SELECT regexp_replace( '2024-03-15', '([0-9]{4})-([0-9]{2})-([0-9]{2})', '\3/\2/\1' );

这里\1、\2、\3分别对应模式里的三个括号分组。输出是15/03/2024。这种写法在数据迁移、日志格式统一时特别常见。

再举个例子，手机号脱敏。假设日志里有完整手机号13812345678，想只保留前三位和后四位：

SELECT regexp_replace( '13812345678', '(1[3-9][0-9])([0-9]{4})([0-9]{4})', '\1****\3' );

用分组把中间四位单独抓出来，替换串里不引用它，换成星号，实现比substring拼字符串更直观。

如果想把一段文本里所有匹配都替换掉，记得加g标志。不加g的话，默认只替换第一个匹配，这个新手最容易踩：

-- 只替换第一个数字 SELECT regexp_replace('a1b2c3', '[0-9]', '#'); -- 替换所有数字 SELECT regexp_replace('a1b2c3', '[0-9]', '#', 'g');

第一条返回a#b2c3，第二条返回a#b#c#。一个是坑，一个是需求，别用混了。

4.2 用正则做条件替换

regexp_replace不只能改格式，还能结合CASE WHEN做业务规则重写。比如把一批脏文本里的地址后缀统一成标准说法：

SELECT raw_address, CASE WHEN raw_address ~ '省$' THEN regexp_replace(raw_address, '省$', '省') WHEN raw_address ~ '市$' THEN raw_address WHEN raw_address ~ '自治州$' THEN regexp_replace(raw_address, '自治州$', '州') ELSE raw_address END AS normalized_address FROM customer_addresses;

如果把“自治州”直接改成“州”省去括号听起来有点粗暴，但实际项目中，订单地区归类经常就是这样“暴力归一”的。正则在这里的价值是：一个模式覆盖多种书写变体，不用写十几个LIKE条件。

我还常用regexp_replace做连续空白的压缩，处理从网页或 Word 里复制过来的文本时特别有效：

SELECT regexp_replace(content, '[[:space:]]+', ' ', 'g') FROM articles;

这里用[[:space:]]而不是\s，是因为 POSIX 字符类在 PostgreSQL 里更稳，后面讲转义时细说。

5. 拆分与定位：regexp_split 系列的正确姿势

有些场景不是“从一段文本里提取内容”，而是“把一段文本按多个分隔符切开”。如果只用split_part这种函数，遇到不规整的分隔符会非常痛苦。PostgreSQL 的regexp_split_to_table和regexp_split_to_array就是干这个的。

5.1 处理脏分隔符

先说最常见的需求：一个字段里存了多个标签，分隔符有时是逗号，有时是分号，有时是全角逗号，甚至混着空格。普通split_part只能按单个固定分隔符切，根本无能为力。正则可以这样写：

SELECT regexp_split_to_table('前端, 后端；中台 运维', '[,;；\\s ]+');

正则模式里把半角逗号、分号、全角分号、空格、全角空格全部放进字符类，一次切干净。输出是四行：

前端 后端 中台 运维

如果想把结果直接变成数组存到text[]列里，用regexp_split_to_array：

SELECT regexp_split_to_array('a,b;c', '[,;]');

输出：

{a,b,c}

这里有个细节一定要记住：当正则匹配到空字符串时，拆分结果可能会带上空字符串。比如regexp_split_to_table('abc', '')会把字符串按字符拆开，但某些模式下可能多出空行。如果业务上不允许空值，建议外面包一层WHERE过滤：

SELECT trim(tag) AS tag FROM items, LATERAL regexp_split_to_table(tags, '[,;]') AS tag WHERE trim(tag) <> '';

LATERAL在 PostgreSQL 里很常用，相当于对每一行执行一次拆分，再把结果展开。这个写法比string_agg拼来拼去干净得多。

5.2 定位第 N 个匹配位置

另一个“让人抓狂”的需求是：我想知道某个正则模式第一次或第二次出现在字段的哪个位置。以前版本里strpos不支持正则，我只能绕着写。

从 PostgreSQL 16 开始，regexp_instr直接解决：

SELECT log_line, regexp_instr(log_line, '[0-9]+', 1, 2) AS second_num_pos FROM app_logs;

第三个参数1表示从第一个字符开始，第四个参数2表示第几次出现。如果找不到，返回 0。

在旧版本里，我一般这样模拟：先取出第 N 次匹配的片段，再用position定位。比如第 2 个数字的位置，可以用：

SELECT strpos( log_line, (regexp_matches(log_line, '[0-9]+', 'g'))[2]::text ) AS second_num_pos FROM app_logs;

注意regexp_matches返回数组，取[2]表示第二个匹配到的数字。这种写法能对付大多数情况，但还是不如 PG16 的regexp_instr直观。所以，“现在到底下载哪个版本、要不要升级”这类问题，我个人的答案是：没有太强的历史包袱就升，正则函数族是真的好用。

6. 性能优化：让正则查询不那么慢

正则虽好，性能问题不能忽视。我见过挺多人写完一个看似优雅的正则 SQL，一执行就发现全表扫描，数据量一上来直接悲剧。正则本身不是不能用，而是要用得有策略。

6.1 正则为什么没法直接走 B-tree 索引

B-tree 索引适合等值、范围、前缀匹配这类查询，但正则的语义是“从字符串任意位置做模式匹配”，数据库无法直接把col ~ 'abc.*def'转换成 B-tree 的范围扫描。所以，大表上频繁跑复杂正则，代价确实高。

最简单的优化思路是：先用普通条件把数据量切小，再在上面做正则过滤。比如查最近一天日志里的错误码：

SELECT * FROM app_logs WHERE created_at > now() - interval '1 day' AND log_line ~ 'ERROR [A-Z0-9_]+';

这样created_at能走普通索引，正则只在小集合里跑，速度会快好几个数量级。这条建议听着很基础，但我见过太多人忽略，一上来就是把整个历史表扫一遍。

还有一个策略是给高频查询的字段建 trigram 索引。PostgreSQL 自带的pg_trgm扩展可以加速LIKE和正则匹配，方式是：

CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_app_logs_line_trgm ON app_logs USING gin (log_line gin_trgm_ops);

这种索引的核心思想是把字符串拆成连续的三字符片段，用 GIN 索引快速定位可能匹配的行。需要注意，不是所有正则都能走 trigram 索引，模式里至少要包含足够多的普通字符，^、$这类锚点或纯数字字符集的匹配可能无法利用索引。所以建完之后，一定要用EXPLAIN ANALYZE看执行计划，别想当然认为建了索引就一定用得上。

6.2 表达式索引、生成列和分批更新

另一种策略是把正则计算的结果保存下来，再用普通索引加速。例如业务上经常按“去掉分隔符后的手机号”查用户：

SELECT * FROM customers WHERE regexp_replace(phone, '[^0-9]', '', 'g') = '13812345678';

如果你在 PostgreSQL 12 及以上版本，可以加一个生成列，把手机号规范化的值直接存下来：

ALTER TABLE customers ADD COLUMN phone_digits text GENERATED ALWAYS AS (regexp_replace(phone, '[^0-9]', '', 'g')) STORED; CREATE INDEX idx_customers_phone_digits ON customers(phone_digits);

之后查询直接查phone_digits就好。这个思路本质是“把复杂消耗前置”：写入时算好，查询时走普通索引。

同理，如果你每次都要从日志里抠错误码，那就应该在写入或导入时把它拆成独立列，而不是每次查询都跑一遍正则提取。对高频查询路径来说，宁可多存一列，也不要牺牲响应速度。

最后说一个非常实在的教训：在大表上做UPDATE ... SET ... regexp_replace(...)这种批量改写，一定要分批。比如一张千万级表，一次性更新会把表锁很久，还可能把连接池打满。我常用的做法是：

UPDATE big_table SET content = regexp_replace(content, '[[:space:]]+', ' ', 'g') WHERE id IN ( SELECT id FROM big_table WHERE content ~ '[[:space:]]{2,}' ORDER BY id LIMIT 10000 );

循环执行，直到UPDATE返回 0 行为止。这样单批影响行数可控，锁时间短，对线上影响小。正则替换看着很酷，但大规模执行前一定要想清楚“这锅饭是闷一大锅还是一锅一锅炒”。

7. 常见问题与排查技巧实录

最后把我在实际使用中遇到的高频问题集中梳理一下，基本每个都让人头疼过。

7.1 转义与反斜杠

PostgreSQL 的字符串转义规则改过好几次，现在默认standard_conforming_strings是开启的，意思是普通字符串里的反斜杠不会再被吃掉。对正则来说，这带来一个常见的混乱。

比如我想匹配一个数字，模式是\d：

-- 这种写法在 16 里能工作 SELECT 'abc123' ~ '\d'; -- 如果你用 E 前缀，双反斜杠是给正则引擎的 SELECT 'abc123' ~ E'\\d';

两种写法殊途同归，都是正则引擎收到两个字符\d。但如果你写成'\\d'，普通字符串会把两个反斜杠原样传给正则引擎，正则引擎看到的可能是“转义反斜杠 + 字母 d”，结果匹配的不是数字。这类问题排查起来特别隐蔽，看起来明明是同一个模式，结果就是没匹配上。

我的经验是：项目里统一一种写法，推荐用 E 前缀：

WHERE phone ~ E'\\d{11}';

或者更稳一点，干脆用 POSIX 字符类，彻底避开反斜杠：

WHERE phone ~ '[0-9]{11}';

后者虽然写得长一点，但不会因为字符串转义配置变化而出错，对后来维护的人来说也更好理解。

7.2 贪婪与非贪婪、换行

正则默认是贪婪匹配。比如一行字符串里有多处 HTML 标签，你想把<b>...</b>之间的内容去掉，如果写成：

SELECT regexp_replace( '<b>第一段</b> 和 <b>第二段</b>', '<b>(.*)</b>', '内容已移除' );

因为.*贪婪，它会从第一个<b>一直匹配到最后一个</b>，把整段“第一段和 第二段”全删掉，只留下“ 和 ”两边的内容。如果你只想去掉每一对标签，要写非贪婪量词：

SELECT regexp_replace( '<b>第一段</b> 和 <b>第二段</b>', '<b>(.*?)</b>', '内容已移除', 'g' );

.*?表示尽量少匹配，遇到第一个</b>就停。这个区别在解析 HTML、JSON 半成品、日志片段时特别常见，不细心真的容易把正确数据一起干掉。

再说换行问题。默认情况下，正则里的.不匹配换行符。如果你要匹配跨行的文本块，有两种办法：一是显式写[\\s\\S]表示“任意字符，包括换行”；二是用内联标志(?s)让点号匹配换行：

SELECT regexp_replace(log_line, '(?s)START.(.*)END', '中间内容已捕获');

内联标志(?s)其实就是“单行模式”，在 PostgreSQL 里是支持的。处理多行日志时非常好用。

7.3 常见问题速查表

下面这个表是我自己平时速查用的，你要抄作业可以直接拿去：

我自己的体会是，PostgreSQL 的表达式能力已经非常接近脚本语言，正则只是其中最顺手的一个代表。关键不在于背多少个函数，而是要养成“先缩范围、再正则”、 “能字符类就少用反斜杠”、 “高频查询用生成列缓存结果”这几个习惯。我第一次在线上环境大规模写正则更新时，也以为 SQL 写得漂亮就够了，结果 2000 万行的表差点被一条正则替换拖垮。从那以后，凡是批量正则处理，我必先看执行计划、再加批处理，这句话送给所有准备在 PostgreSQL 里“优雅”一把的同行。