PostgreSQL模式匹配与日期处理详解

Oliver

发布时间：2026-10-13| 6人浏览过

原创

掌握PostgreSQL中的模式匹配与时间处理是优化查询的关键。本文深入解析 LIKE 与 SIMILAR TO 的通配符差异、substring 函数的截取技巧，并系统化梳理日期/时间格式化模板、算术操作符及 EXTRACT 等核心函数，为 SQL 开发提供实用的参考指南。

模式匹配实战

PostgreSQL 提供了三种主要的模式匹配方式：标准的 LKE、更贴近正则的 SIMILAR TO，以及 POSIX 风格正则表达式。理解它们的异同点能显著提升数据筛选的效率。

目的：使用标准 SQL 方式进行基础字符匹配，支持大小写忽略及自定义转义。

基础语法：使用 string LIKE pattern [ESCAPE escape-character] 进行匹配。下划线 (_) 匹配单个任意字符，百分号 (%) 匹配零个或多个字符。
特殊处理：若需匹配字面量的 _ 或 %，需使用转义字符（默认反斜杠）。可通过 ESCAPE '' 关闭转义机制，此时通配符功能保留但不可转义。
大小写敏感：关键字 ILIKE 可实现不区分大小写的匹配（PostgreSQL 特有）。
替代符号：~~ 等效于 LKE，~~* 等效于 ILIKE。

示例代码:

'abc' LIKE 'a%'        -- true
'abc' LIKE '_b_'       -- true
'abc' ILIKE 'ABC%'     -- true (不区分大小写)

目的：在不完全使用复杂正则的前提下，利用类似正则的元字符（如 |, *, +）进行灵活的模式构造。

兼容通配符：继承 LIKE 的 _ 和 % 含义。
扩展元字符：支持 | (选择), * (零次或多次), + (一次或多次), () (分组) 及 [] (字符表)。
提取子串：可使用三参数形式的 substring(string from pattern for escape-char)。此模式下，模式必须匹配整个字符串才能成功，需通过双引号标记期望返回的部分。

示例代码:

-- 包含 b 或 d 的字符串
'abc' SIMILAR TO '%(b|d)%' -- true

-- substring 提取：#为转义符，双引号内为捕获内容
SELECT substring('foobar' from '%#"o_b#"%' FOR '#');
-- 结果: oob

PostgreSQL 内置了丰富的格式化函数，用于将日期、整数、浮点数等多种类型转换为格式化的字符串，反之亦然。其通用调用习惯为：第一个参数为待格式化值，第二个参数为定义输出模板的文本。

函数	返回类型	描述	例子
to_char(timestamp, text)	text	把时间戳转换成字串	to_char(current_timestamp, 'HH12:MI:SS')
to_date(text, text)	date	把字串转换成日期	to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把字串转换成numeric	to_number('12,454.8-', '99G999D9S')

在使用 to_char 时，需要了解以下两种核心模板：

PostgreSQL 提供了强大的时间运算能力，包括标准的四则运算操作符以及提取特定时间片段的系统函数。

以下表格展示了日期、时间和间隔 (Interval) 之间的混合运算行为：

操作符	例子	结果类型
+	date + interval	timestamp
-	date - integer (天数)	date
-	date - date	integer (相差的天数)
-	time - time	interval
* / /	interval * double / interval / double	interval

常用的时间获取与处理函数如下：

这两个函数等效，均用于从 timestamp 或 interval 中提取特定的时间域。