PostgreSQL Operators 运算符实用教程

本教程按数据类型分类介绍 PostgreSQL 常用运算符。运算符的含义由操作数类型决定；同一个符号（例如 +、@>）可能在不同类型上代表不同操作。

运算符速览

类型类别	常见运算符	示例用途
通用比较与逻辑	`=`、`<>`、`<`、`AND`、`OR`、`IS NULL`	筛选、组合条件
Numeric 数值	`+`、`-`、`*`、`/`、`%`、`^`	算术计算
Character / Text	`
Date/Time	`+`、`-`、比较	日期偏移、时间差
ARRAY	`@>`、`<@`、`&&`、`=`	包含、重叠、相等
JSONB	`->`、`->>`、`@>`、`?`	提取、包含、键存在性
Range	`@>`、`<@`、`&&`、`*`	包含、重叠、交集
Network / Bit string	`<<`、`>>`、`&`、`	`
全文搜索	`@@`、`&&`、`

通用比较、NULL 与逻辑运算

大多数标量类型可用比较运算符。布尔条件支持 AND、OR、NOT；NULL 使用三值逻辑，因此普通比较遇到 NULL 会得到 unknown（在 WHERE 中不会被选中）。

SELECT 10 = 10;
-- true
SELECT 10 <> 3;
-- true
SELECT NULL = NULL;
-- NULL，不是 true
SELECT * FROM users WHERE email IS NOT NULL AND (status = 'active' OR is_admin IS TRUE);
-- NULL 安全的相等判断
SELECT NULL IS NOT DISTINCT FROM NULL;
-- true

IS DISTINCT FROM 和 IS NOT DISTINCT FROM 把 NULL 当作可比较状态，适合需要 NULL 安全相等判断的场景。集合比较还可使用 IN、ANY、ALL。

SELECT 3 IN (1, 3, 5);
-- true
SELECT 3 = ANY (ARRAY[1, 3, 5]);
-- true
SELECT 3 < ALL (ARRAY[5, 8, 10]);
-- true

注意

NOT IN 的集合中若包含 NULL，结果可能是 NULL 而不是 true。对可空子查询，通常用 NOT EXISTS 表达“没有匹配行”更清晰。

Numeric：数值运算符

整数、精确数值和浮点数共享常见算术符号，但除法和精度行为取决于操作数类型。

SELECT 7 + 2, 7 - 2, 7 * 2;
SELECT 7 / 2;
-- integer 除法：3
SELECT 7::numeric / 2;
-- numeric 除法：3.5
SELECT 7 % 2;
-- 取余：1
SELECT 2 ^ 3;
-- 幂运算（numeric / float 类型）
SELECT 5 !;
-- 阶乘（numeric）
-- 比较数值大小
SELECT 10.25::numeric > 9.99::numeric;

信息

金额计算一般使用 numeric。浮点数比较受近似表示影响，不要假设复杂运算结果总能精确相等。需要截断或取整时，应明确选择 round、trunc、floor 或 ceil 等函数。

Character / Text：字符串运算符

|| 连接字符串；LIKE 和 ILIKE 用于通配符匹配；~、~* 用于 POSIX 正则表达式匹配。

SELECT 'Post' || 'greSQL';
-- PostgreSQL
SELECT 'postgresql' LIKE 'post%';
-- false：LIKE 区分大小写
SELECT 'postgresql' ILIKE 'Post%';
-- true：ILIKE 忽略大小写
SELECT 'user42' ~ '^[a-z]+[0-9]+$';
-- true：正则匹配
SELECT 'abc' SIMILAR TO '(a|b)%';
-- SQL 正则样式

% 匹配任意长度字符串；_ 匹配单个字符。
LIKE 区分大小写；ILIKE 是 PostgreSQL 提供的大小写不敏感匹配。
正则表达式中 ~* 表示不区分大小写匹配；!~ 和 !~* 表示不匹配。

注意

字符串拼接遇到 NULL 时通常会得到 NULL；若要把 NULL 当空字符串处理，使用 concat 或 coalesce。用户输入若包含 LIKE 特殊字符，要按需求转义 %、_ 和转义字符。

Date / Time：日期时间运算符

日期、时间戳和 interval 可使用 +、- 进行有类型语义的计算。

SELECT date '2026-10-20' + 7;
-- 日期加整数天数
SELECT date '2026-10-20' - date '2026-10-01';
-- 相差天数
SELECT timestamp '2026-10-20 09:00' + interval '90 minutes';
SELECT timestamptz '2026-10-20 09:00+08' - timestamptz '2026-10-20 08:00+08';
-- interval
SELECT interval '2 hours' * 3;

信息

date + integer 表示增加天数；timestamp 与 interval 相加得到新的时间；两个时间点相减得到间隔。日历月不等于固定天数，涉及夏令时的时间点运算也应明确时区语义。

Boolean：布尔运算符

布尔逻辑支持 AND、OR、NOT。NULL 表示 unknown，组合时遵循 SQL 三值逻辑。

SELECT true AND false;
-- false
SELECT true OR NULL;
-- true
SELECT NOT NULL;
-- NULL
SELECT * FROM tasks WHERE is_done IS FALSE AND (priority = 'high' OR is_blocked IS TRUE);

可用 IS TRUE、IS FALSE、IS UNKNOWN 明确判断布尔状态。不要把 SQL 的 unknown 等同于 false。

ARRAY：数组运算符

数组的包含、被包含和重叠运算适合检查集合成员关系。数组是否相等要求元素顺序等数组结构一致；包含判断不等价于普通集合的多重集语义。

SELECT ARRAY[1, 2, 3] @> ARRAY[2];
-- 左侧包含右侧元素
SELECT ARRAY[2] <@ ARRAY[1, 2, 3];
-- 左侧被右侧包含
SELECT ARRAY[1, 2] && ARRAY[2, 4];
-- 有共同元素
SELECT ARRAY[1, 2] || ARRAY[3, 4];
-- 数组合并/连接
-- 常见的数组元素查询
SELECT * FROM products WHERE tags @> ARRAY['sale'];

数组元素可以用 array[index] 取值，也可用 ANY 比较标量与数组元素：'sale' = ANY(tags)。若需要对元素分别建立关系、外键或复杂查询，通常应改用子表建模。

JSON / JSONB：JSON 运算符

JSON 操作符用于提取字段、检查键和值包含关系。右侧整数表示数组索引（从 0 开始），右侧文本表示对象键。

SELECT '{"user":{"name":"Ada"},"tags":["db","sql"]}'::jsonb -> 'user';
SELECT '{"user":{"name":"Ada"}}'::jsonb #> '{user,name}';
SELECT '{"user":{"name":"Ada"}}'::jsonb #>> '{user,name}';
SELECT '{"type":"signup"}'::jsonb @> '{"type":"signup"}'::jsonb;
SELECT '{"type":"signup"}'::jsonb ? 'type';
-- 顶层键是否存在
SELECT '{"a":1,"b":2}'::jsonb - 'a';
-- 删除键
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb;
-- 合并对象

运算符	含义	返回类型
`->` / `#>`	按键、数组下标或路径提取 JSON	`json` / `jsonb`
`->>` / `#>>`	提取为文本	`text`
`@>` / `<@`	JSONB 包含关系	`boolean`
`?`、`?	`、`?&`	检查一个、任意一个或全部顶层键/数组字符串元素是否存在
`		`、`-`、`#-`

注意

JSONB 的 ? 只检查顶层键或顶层数组字符串元素，不会递归搜索任意嵌套层级。提取到不存在的路径通常返回 NULL。更新深层结构时，检查路径和字段类型，避免把 JSON 数字、字符串和 SQL 文本混淆。

Range：范围运算符

范围运算符表达包含、重叠、相邻和交集，适合日期排期、价格区间及有效期查询。

SELECT int4range(1, 10, '[)') @> 5;
-- 包含元素 5
SELECT int4range(1, 10) && int4range(8, 12);
-- 区间重叠
SELECT int4range(1, 5) -|- int4range(5, 9);
-- 相邻
SELECT int4range(1, 10) * int4range(5, 15);
-- 交集
SELECT int4range(1, 10) - int4range(7, 10);
-- 差集：[1, 7)
-- 使用 GiST 排除重叠预订（等值标量与范围组合时需要 btree_gist）
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings ( room_id integer NOT NULL, during tstzrange NOT NULL, EXCLUDE USING gist (room_id WITH =, during WITH &&) );

@> / <@ 判断包含；&& 判断重叠；-|- 判断相邻；* 求交集。范围的开闭边界会影响是否包含端点，建模时要统一边界约定。

Network Address：网络地址运算符

inet 和 cidr 提供专门的地址、网段包含和掩码运算，避免把 IP 当普通字符串处理。

SELECT inet '192.168.1.25' << cidr '192.168.1.0/24';
-- 属于子网
SELECT inet '192.168.1.25' >> cidr '192.168.1.0/24';
-- 不属于子网
SELECT inet '192.168.1.25' & inet '255.255.255.0';
-- 地址按位 AND
SELECT inet '192.168.1.25' + 1;
-- 地址递增
SELECT inet '192.168.1.25' > inet '192.168.1.10';

<< / >> 用于判断地址是否位于网络内或网络外；& 可应用整数表示的网络掩码。使用 inet / cidr 运算符比字符串前缀比较可靠。

Bit String：位串运算符

位串支持按位逻辑和移位。操作数长度应符合运算要求；它们与整数的位运算不是同一套类型操作。

SELECT B'1010' & B'1100';
-- AND: 1000
SELECT B'1010' | B'1100';
-- OR: 1110
SELECT B'1010' # B'1100';
-- XOR: 0110
SELECT ~ B'1010';
-- NOT
SELECT B'1010' << 1;
-- 左移
SELECT B'1010' >> 1;
-- 右移

位串下标与数组不同：位串从左侧第 1 位开始编号。固定长度 bit(n) 与可变长度 varbit 在转换、补齐和长度上有所区别。

Text Search：全文搜索运算符

tsvector 表示规范化文档，tsquery 表示搜索条件。匹配运算符 @@ 判断文档是否满足查询。

SELECT to_tsvector('english', 'A database stores information') @@ plainto_tsquery('english', 'database information');
-- tsquery 的组合：AND、OR、NOT
SELECT to_tsquery('english', 'database & information');
SELECT to_tsquery('english', 'database | information');
SELECT to_tsquery('english', '!spam');

tsquery && tsquery：AND，两个条件都要满足。
tsquery || tsquery：OR，满足任一条件。
!! tsquery：NOT，取反搜索条件。
tsvector @@ tsquery：全文匹配，结果为布尔值。

搜索配置决定分词和词干化规则；自然语言文本应使用与内容语言匹配的配置。常见索引为 GIN，查询计划需结合索引和配置验证。

ENUM、UUID 与其他类型

不是每种类型都有一整套专用符号运算。枚举和 UUID 通常用相等、不等和排序比较；自定义类型则取决于为该类型定义的运算符。

CREATE TYPE ticket_state AS ENUM ('open', 'closed');
SELECT 'open'::ticket_state < 'closed'::ticket_state;
SELECT '00000000-0000-0000-0000-000000000001'::uuid < '00000000-0000-0000-0000-000000000002'::uuid;

枚举比较顺序来自创建枚举时声明的顺序，不是按标签字母排序。UUID 的顺序比较是 UUID 类型的排序语义，不应把它当成时间先后关系来依赖。

运算符重载、优先级与索引

PostgreSQL 根据操作数类型解析运算符。相同符号用于不同类型时可能表示完全不同的含义，例如 + 可做数值加法、日期偏移或数组连接，@> 可用于范围、数组或 JSONB 包含关系。

类型不明确或存在多个候选时，使用 ::type 显式转换。
用括号表达预期分组，不要依赖记忆运算符优先级。
NULL 比较、整数除法和日期时间时区是常见的语义陷阱。
索引能否支持某个运算符取决于索引类型和 operator class；用 EXPLAIN 检查实际查询计划。
GIN 常用于数组、JSONB 和全文搜索；GiST 常用于范围、网络或空间类查询，但具体支持情况要看操作符类。

-- 显式转换让运算符类型和结果更清晰
SELECT '10'::integer + 5;
-- 对代表性查询检查执行计划
EXPLAIN SELECT * FROM events WHERE payload @> '{"type":"signup"}'::jsonb;

运算符速览​

通用比较、NULL 与逻辑运算​

Numeric：数值运算符​

Character / Text：字符串运算符​

Date / Time：日期时间运算符​

Boolean：布尔运算符​

ARRAY：数组运算符​

JSON / JSONB：JSON 运算符​

Range：范围运算符​

Network Address：网络地址运算符​

Bit String：位串运算符​

Text Search：全文搜索运算符​

ENUM、UUID 与其他类型​

运算符重载、优先级与索引​