PostgreSQL 除了常见的 =、>、< 之外，还有一大堆特殊操作符。这些操作符在处理 JSON、数组、范围类型、全文搜索时非常强大。我把它们按使用频率分成常用和不常用两类，方便快速查找。
记忆技巧
PostgreSQL 的操作符虽然多，但有规律可循：
| 符号规律 | 含义 |
|---|---|
@ 开头 | 包含、搜索相关 |
> < 组合 | 方向、位置、范围 |
双符号 >> << | 严格关系 |
& | 交集、重叠 |
| | 并集、连接 |
-> ->> | JSON 提取（单箭头返回 JSON，双箭头返回文本） |
~ | 正则匹配 |
# | 路径、异或 |
重点记住这几个：
@>/<@：包含关系（JSON、数组、范围通用）->/->>：JSON 提取~/~*：正则匹配&&：重叠判断（数组、范围、几何通用）@@：全文搜索
常用操作符
JSON/JSONB 操作符
现在后端存储 JSON 数据太常见了，这几个操作符是高频使用的：
| 操作符 | 含义 | 例子 |
|---|---|---|
-> | 按键取 JSON | SELECT '{"a":1}'::json->'a'; — 1 (json) |
->> | 按键取文本 | SELECT '{"a":1}'::json->>'a'; — “1” (text) |
@> | JSON 包含 | SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'; — true |
? | 键是否存在 | SELECT '{"a":1}'::jsonb ? 'a'; — true |
#> | 按路径取 JSON | SELECT '{"a":{"b":2}}'::json#>'{a,b}'; — 2 (json) |
#>> | 按路径取文本 | SELECT '{"a":{"b":2}}'::json#>>'{a,b}'; — “2” (text) |
?| | 任一键存在 | SELECT '{"a":1,"b":2}'::jsonb ?| array['b','c']; — true |
?& | 所有键存在 | SELECT '{"a":1,"b":2}'::jsonb ?& array['a','b']; — true |
使用场景：API 响应存储、配置数据、动态字段等。
字符串模式匹配
搜索和匹配文本是最基础的需求：
| 操作符 | 含义 | 例子 |
|---|---|---|
LIKE | SQL 模式匹配 | SELECT 'hello' LIKE 'h%'; — true |
ILIKE | 不区分大小写 LIKE | SELECT 'Hello' ILIKE 'h%'; — true |
~ | 匹配正则（区分大小写） | SELECT 'hello' ~ 'h.*o'; — true |
~* | 匹配正则（不区分大小写） | SELECT 'Hello' ~* 'HELLO'; — true |
!~ | 不匹配正则（区分） | SELECT 'hello' !~ 'xyz'; — true |
!~* | 不匹配正则（不区分） | SELECT 'Hello' !~* 'xyz'; — true |
SIMILAR TO | SQL 标准正则 | SELECT 'hello' SIMILAR TO 'h%o'; — true |
使用场景：用户搜索、日志过滤、数据清洗。
数组操作符
当数据结构是数组时，这些操作符能简化很多查询逻辑：
| 操作符 | 含义 | 例子 |
|---|---|---|
@> | 数组包含 | SELECT ARRAY[1,2,3] @> ARRAY[2,3]; — true |
<@ | 数组被包含 | SELECT ARRAY[2] <@ ARRAY[1,2,3]; — true |
&& | 数组重叠（有共同元素） | SELECT ARRAY[1,2] && ARRAY[2,3]; — true |
|| | 数组连接 | SELECT ARRAY[1,2] || ARRAY[3,4]; — {1,2,3,4} |
使用场景：标签系统、权限判断、多选字段。
比较操作符（扩展）
这些是 SQL 标准操作符的扩展，用来简化复杂条件：
| 操作符 | 含义 | 例子 |
|---|---|---|
IN (...) | 在列表中 | SELECT 5 IN (1, 3, 5, 7); — true |
NOT IN (...) | 不在列表中 | SELECT 5 NOT IN (1, 2, 3); — true |
BETWEEN ... AND ... | 在范围内 | SELECT 5 BETWEEN 1 AND 10; — true |
NOT BETWEEN | 不在范围内 | SELECT 5 NOT BETWEEN 10 AND 20; — true |
ANY/SOME | 任满足 | SELECT 5 = ANY(ARRAY[1,3,5]); — true |
ALL | 全满足 | SELECT 5 > ALL(ARRAY[1,2,3]); — true |
EXISTS | 子查询有结果 | SELECT EXISTS(SELECT 1 FROM t WHERE id=1); |
BETWEEN SYMMETRIC | 自动调整边界顺序 | SELECT 5 BETWEEN SYMMETRIC 10 AND 1; — true |
使用场景：批量查询、条件过滤、子查询判断。
NULL 处理
NULL 处理不当会导致 Bug，这些操作符能让逻辑更清晰：
| 操作符 | 含义 | 例子 |
|---|---|---|
IS NULL | 是 NULL | SELECT * FROM t WHERE col IS NULL; |
IS NOT NULL | 不是 NULL | SELECT * FROM t WHERE col IS NOT NULL; |
IS DISTINCT FROM | 不同（把 NULL 当值） | SELECT NULL IS DISTINCT FROM NULL; — false |
IS NOT DISTINCT FROM | 相同（把 NULL 当值） | SELECT NULL IS NOT DISTINCT FROM NULL; — true |
使用场景：数据校验、兼容性处理、防止三值逻辑陷阱。
范围类型操作符
时间段、价格区间、IP 段等场景会用到范围类型：
| 操作符 | 含义 | 例子 |
|---|---|---|
@> | 包含元素 | SELECT int4range(1,10) @> 5; — true |
<@ | 被包含 | SELECT 5 <@ int4range(1,10); — true |
&& | 范围重叠 | SELECT int4range(1,5) && int4range(3,8); — true |
-|- | 相邻 | SELECT int4range(1,5) -|- int4range(5,10); — true |
<< | 严格在左 | SELECT int4range(1,3) << int4range(5,10); — true |
>> | 严格在右 | SELECT int4range(5,10) >> int4range(1,3); — true |
+ | 范围并集 | SELECT int4range(1,5) + int4range(5,10); — [1,10) |
使用场景：预订系统、时间段查询、区间统计。
全文搜索操作符
做站内搜索时，全文搜索比 LIKE 性能好太多：
| 操作符 | 含义 | 例子 |
|---|---|---|
@@ | 匹配查询 | SELECT to_tsvector('cat') @@ to_tsquery('cat'); — true |
|| | 连接 | SELECT to_tsvector('cat') || to_tsvector('dog'); |
使用场景：文章搜索、关键词匹配、内容推荐。
不常用操作符
这些操作符在特定场景下很有用，但日常开发中不常碰到。
几何类型操作符
地理位置、图形计算、空间数据库会用到：
| 操作符 | 含义 | 例子 |
|---|---|---|
<-> | 距离 | SELECT point(0,0) <-> point(3,4); — 5 |
&& | 重叠 | SELECT box '(0,0),(2,2)' && box '(1,1),(3,3)'; — true |
<< | 严格在左 | SELECT point(1,2) << point(3,4); — true |
>> | 严格在右 | SELECT point(3,4) >> point(1,2); — true |
<^ | 严格在下 | SELECT point(1,2) <^ point(1,5); — true |
>^ | 严格在上 | SELECT point(1,5) >^ point(1,2); — true |
~= | 相同位置 | SELECT point(1,2) ~= point(1,2); — true |
@ | 面积/长度 | SELECT @ circle '<(0,0),1>'; — 3.14159… |
## | 交点 | SELECT box '(0,0),(2,2)' ## box '(1,1),(3,3)'; |
>>= | 包含或在上 | SELECT polygon '((0,0),(2,0),(2,2),(0,2))' >>= point(1,1); |
<<| | 在左或在边界 | SELECT point(0,0) <<| point(2,2); |
使用场景：GIS 应用、地图服务、附近的人。
网络地址操作符
网络安全、IP 管理、防火墙规则会用到：
| 操作符 | 含义 | 例子 |
|---|---|---|
<< | 包含于网络 | SELECT '192.168.1.5'::inet << '192.168.1.0/24'::inet; — true |
>> | 包含网络 | SELECT '192.168.1.0/24'::inet >> '192.168.1.5'::inet; — true |
&& | 重叠 | SELECT '192.168.1.0/24'::inet && '192.168.1.128/25'::inet; — true |
<<= | 包含于或等于 | SELECT '192.168.1.0/24'::inet <<= '192.168.1.0/24'::inet; — true |
>>= | 包含或等于 | 同上 |
使用场景：IP 白名单、子网计算、网络监控。
位运算操作符
底层开发、权限系统、状态机会用到：
| 操作符 | 含义 | 例子 |
|---|---|---|
& | 与 | SELECT B'1010' & B'1100'; — 1000 |
| | 或 | SELECT B'1010' | B'1100'; — 1110 |
# | 异或 | SELECT B'1010' # B'1100'; — 0110 |
~ | 非 | SELECT ~ B'1010'; — 0101 |
<< | 左移 | SELECT B'1010' << 2; — 101000 |
>> | 右移 | SELECT B'1010' >> 1; — 0101 |
使用场景：位标记、权限位运算、状态压缩。
其他特殊操作符
一些非常特定场景下的操作符：
| 操作符 | 含义 | 例子 |
|---|---|---|
= | UUID 相等 | SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid = ... |
IS DOCUMENT | XML 是完整文档 | SELECT xmlcol IS DOCUMENT FROM t; |
IS NOT DOCUMENT | XML 不是完整文档 | SELECT xmlcol IS NOT DOCUMENT FROM t; |
使用场景：UUID 主键、XML 解析。