算术与比较运算符
算术运算符用于数值计算，比较运算符用于判断两值关系——它们是所有 WHERE、SELECT、JOIN 条件的基石。本节讲解各运算符号的行为差异、整数除法与除零陷阱、NULL 比较的语义，以及 IS DISTINCT FROM 这类特殊比较谓词。
前置知识
阅读本节前，建议先了解：行锁查询（FOR UPDATE）
基础概念
术语对照
| 中文 | English | 说明 |
|---|---|---|
| 运算符 | Operator | 对操作数执行运算的符号 |
| 操作数 | Operand | 参与运算的值或表达式 |
| 一元运算符 | Unary Operator | 只接受一个操作数，如 -x |
| 二元运算符 | Binary Operator | 接受两个操作数，如 a + b |
| 优先级 | Precedence | 决定运算先后顺序 |
| 结合性 | Associativity | 同优先级时的结合方向 |
| 重载 | Operator Overloading | 同名运算符适配多种类型 |
| 隐式转换 | Implicit Cast | 类型不同时自动提升类型 |
运算符即函数
PostgreSQL 中，运算符本质是函数调用的语法糖。a + b 实际调用 +(a, b)，而且同一个符号可以针对不同类型有多套实现：
sql
-- 查看 + 运算符的所有重载
SELECT oprname, oprleft::regtype AS left_type, oprright::regtype AS right_type
FROM pg_operator
WHERE oprname = '+'
ORDER BY left_type::text, right_type::text
LIMIT 10;text
oprname | left_type | right_type
---------+-----------+------------
+ | aclitem | aclitem
+ | bigint | bigint
+ | box | box
+ | date | integer
+ | date | interval
+ | integer | bigint
+ | integer | integer
+ | interval | interval
+ | jsonb | integer
+ | numeric | numeric
(10 rows)因此同一个 + 在 1 + 2 上是整数加法，在 '2026-01-01'::date + 7 上是日期偏移，在 '{...}'::jsonb + 1 上又是 JSON 操作。类型决定了实际行为。
示例数据
后续示例使用 demo 库中的 products 表：
sql
CREATE TABLE products (
sku varchar(20) PRIMARY KEY,
name varchar(50) NOT NULL,
category varchar(20) NOT NULL,
price numeric(10,2) NOT NULL,
cost numeric(10,2) NOT NULL,
stock integer NOT NULL,
rating numeric(2,1),
launched date
);
INSERT INTO products (sku, name, category, price, cost, stock, rating, launched) VALUES
('SKU-1001', '显示器支架', 'peripherals', 120.00, 60.00, 120, 4.5, '2024-03-01'),
('SKU-1002', 'USB-C 线缆', 'peripherals', 30.00, 10.00, 300, 4.0, '2024-05-10'),
('SKU-1003', '机械键盘', 'input', 299.00, 180.00, 80, 4.7, '2024-07-15'),
('SKU-1004', '无线鼠标', 'input', 89.00, 45.00, 200, 4.2, '2024-09-20'),
('SKU-1005', '显示器', 'display', 1200.00, 900.00, 25, 4.8, '2025-01-05'),
('SKU-1006', 'HDMI 线缆', 'peripherals', 59.00, 20.00, 500, 3.9, '2025-02-18'),
('SKU-1007', '扩展坞', 'peripherals', 399.00, 260.00, 60, 4.6, '2025-04-22'),
('SKU-1008', '投影仪', 'display', 2999.00,2400.00, 10, 4.4, '2025-06-30'),
('SKU-1009', '摄像头', 'input', 199.00, 130.00, 150, 4.9, NULL),
('SKU-1010', '音箱', 'audio', 449.00, 300.00, 0, 4.1, NULL);text
sku | name | category | price | cost | stock | rating | launched
-----------+-------------+-------------+---------+----------+-------+--------+------------
SKU-1001 | 显示器支架 | peripherals | 120.00 | 60.00 | 120 | 4.5 | 2024-03-01
SKU-1002 | USB-C 线缆 | peripherals | 30.00 | 10.00 | 300 | 4.0 | 2024-05-10
SKU-1003 | 机械键盘 | input | 299.00 | 180.00 | 80 | 4.7 | 2024-07-15
SKU-1004 | 无线鼠标 | input | 89.00 | 45.00 | 200 | 4.2 | 2024-09-20
SKU-1005 | 显示器 | display | 1200.00 | 900.00 | 25 | 4.8 | 2025-01-05
SKU-1006 | HDMI 线缆 | peripherals | 59.00 | 20.00 | 500 | 3.9 | 2025-02-18
SKU-1007 | 扩展坞 | peripherals | 399.00 | 260.00 | 60 | 4.6 | 2025-04-22
SKU-1008 | 投影仪 | display | 2999.00 | 2400.00 | 10 | 4.4 | 2025-06-30
SKU-1009 | 摄像头 | input | 199.00 | 130.00 | 150 | 4.9 |
SKU-1010 | 音箱 | audio | 449.00 | 300.00 | 0 | 4.1 |
(10 rows)语法与用法
算术运算符一览
| 运算符 | 名称 | 示例 | 结果 | 说明 |
|---|---|---|---|---|
+ | 加 | 3 + 2 | 5 | 也可用于日期偏移、JSON 合并 |
- | 减 | 3 - 2 | 1 | 一元负号 -x 也用它 |
* | 乘 | 3 * 2 | 6 | 整数相乘可能溢出 |
/ | 除 | 7 / 2 | 3 | 整数除法截断 |
% | 取模 | 7 % 3 | 1 | 余数，符号同被除数 |
^ | 幂 | 2 ^ 10 | 1024 | 返回 double precision |
|/ | 平方根 | |/ 16 | 4 | 返回 double precision |
||/ | 立方根 | ||/ 27 | 3 | 返回 double precision |
@ | 绝对值 | @ -5 | 5 | 一元前缀运算符 |
|| | 字符串拼接 | 'a' || 'b' | 'ab' | 拼接运算符，非算术 |
比较运算符一览
| 运算符 | 含义 | 示例 | 结果 |
|---|---|---|---|
= | 等于 | 2 = 2 | true |
<> 或 != | 不等于 | 2 <> 3 | true |
< | 小于 | 2 < 3 | true |
> | 大于 | 3 > 2 | true |
<= | 小于等于 | 2 <= 2 | true |
>= | 大于等于 | 3 >= 4 | false |
IS DISTINCT FROM | 不等（NULL 安全） | NULL IS DISTINCT FROM 1 | true |
IS NOT DISTINCT FROM | 相等（NULL 安全） | NULL IS NOT DISTINCT FROM NULL | true |
BETWEEN | 区间（含端点） | 2 BETWEEN 1 AND 3 | true |
IN | 属于列表 | 2 IN (1, 2, 3) | true |
LIKE / ILIKE | 模式匹配 | 'abc' LIKE 'a%' | true |
~ / ~* | POSIX 正则 | 'abc' ~ '^a' | true |
详细说明
整数除法会截断
这是最容易被忽视的陷阱。当两个操作数都是整数类型时，/ 执行整数除法，直接丢掉小数部分（不是四舍五入，而是向零截断）：
sql
SELECT 7 / 2 AS int_div, -- 整数除法
7.0 / 2 AS num_div, -- numeric 除法
7 / 2.0 AS num_div2,
7::numeric / 2 AS cast_div;text
int_div | num_div | num_div2 | cast_div
---------+---------+----------+----------
3 | 3.5000 | 3.5000 | 3.5000
(1 row)在业务字段上极易出错：
sql
-- 错误！两个整数列相除，结果被截断
SELECT sku, price / cost AS markup FROM products;
-- 正确：显式转 numeric
SELECT sku, round(price / cost, 2) AS markup FROM products;
-- 或
SELECT sku, round(price::numeric / cost, 2) AS markup FROM products;text
sku | markup
-----------+--------
SKU-1001 | 2.00
SKU-1002 | 3.00
SKU-1003 | 1.66
SKU-1004 | 1.98
SKU-1005 | 1.33
(5 rows)除零与取模
sql
SELECT 1 / 0;text
ERROR: division by zerosql
-- 先判断分母即可规避（NULLIF 是常用手段）
SELECT 1 / NULLIF(0, 0) AS safe_div;text
safe_div
----------
NULL
(1 row)取模的符号跟随被除数（与被除数的符号一致），这一点与某些语言不同：
sql
SELECT 7 % 3 AS pos, -- 1
-7 % 3 AS neg, -- -1，符号跟随被除数
7 % -3 AS neg2, -- 1
-7 % -3 AS neg3; -- -1text
pos | neg | neg2 | neg3
-----+-----+------+------
1 | -1 | 1 | -1
(1 row)幂、根与绝对值
sql
SELECT 2 ^ 10 AS pow2, -- 幂
|/ 16 AS sqrt16, -- 平方根
||/ 27 AS cbrt27, -- 立方根
@ -5 AS abs_val; -- 绝对值text
pow2 | sqrt16 | cbrt27 | abs_val
--------+--------+--------+---------
1024 | 4 | 3 | 5
(1 row)^、|/、||/ 都返回 double precision，参与金额计算时要注意浮点误差。求幂更推荐用函数形式：
sql
SELECT power(2, 10) AS pow2, sqrt(16) AS sqrt16, cbrt(27) AS cbrt27, abs(-5) AS abs_val;一元正负号与优先级
一元 - 优先级高于二元运算符，因此 -2 ^ 2 会被解析为 (-2) ^ 2：
sql
SELECT -2 ^ 2 AS a, -(2 ^ 2) AS b;text
a | b
---+----
4 | -4
(1 row)这与数学上 -2² = -4 的约定不同，写代码时要显式加括号避免歧义。
字符串拼接
|| 是拼接运算符，NULL 参与拼接结果为 NULL：
sql
SELECT 'hello' || ' ' || 'world' AS greeting,
'x' || NULL AS with_null,
'x' || coalesce(NULL, '') AS fixed,
'v' || 42 AS mixed; -- 数字会自动转文本text
greeting | with_null | fixed | mixed
--------------+-----------+-------+-------
hello world | | x | v42
(1 row)concat() 函数则会忽略 NULL，行为不同：
sql
SELECT concat('x', NULL, 'y') AS c;text
c
---
xy
(1 row)比较运算符与隐式类型转换
比较要求两侧类型可比。类型不同时 PostgreSQL 会尝试隐式转换，若无法转换则报错：
sql
-- 整数与 numeric 比较：自动提升，安全
SELECT 1 = 1.0 AS a;
-- 文本与整数比较：PostgreSQL 会把未定类型的字面量转成列的类型
SELECT '10' = '10' AS b;
-- 显式转换更稳妥
SELECT 10 = '10'::integer AS c;text
a | b | c
---+---+---
t | t | t
(1 row)类型转换失败会直接报错：
sql
SELECT 'abc'::integer;text
ERROR: invalid input syntax for type integer: "abc"NULL 参与比较
NULL 表示"未知"，任何与 NULL 的比较（除 IS DISTINCT FROM 系列外）都返回 NULL，而 NULL 在 WHERE 中按 false 处理，因此行被过滤：
sql
-- 这些都返回 NULL，不是 true
SELECT NULL = NULL AS eq, -- NULL
NULL <> NULL AS ne, -- NULL
NULL > 1 AS gt, -- NULL
1 = NULL AS eq2; -- NULLtext
eq | ne | gt | eq2
----+----+----+-----
| | |
(1 row)sql
-- rating 为 NULL 的两行不会出现在结果中
SELECT sku, rating FROM products WHERE rating > 4.0;text
sku | rating
-----------+--------
SKU-1001 | 4.5
SKU-1003 | 4.7
SKU-1004 | 4.2
SKU-1005 | 4.8
SKU-1007 | 4.6
SKU-1008 | 4.4
SKU-1009 | 4.9
(7 rows)SKU-1010 的 rating 为 4.1，大于 4.0，因此它出现在结果中；而 launched 为 NULL 的行若参与比较则会被过滤——这就是 NULL 的三值逻辑在 WHERE 中的表现。
sql
-- 刻意让 NULL 参与比较：这些行不会出现在结果中
SELECT sku, name, launched FROM products WHERE launched > DATE '2024-01-01';text
sku | name | launched
-----------+-------------+------------
SKU-1001 | 显示器支架 | 2024-03-01
SKU-1002 | USB-C 线缆 | 2024-05-10
SKU-1003 | 机械键盘 | 2024-07-15
SKU-1004 | 无线鼠标 | 2024-09-20
SKU-1005 | 显示器 | 2025-01-05
SKU-1006 | HDMI 线缆 | 2025-02-18
SKU-1007 | 扩展坞 | 2025-04-22
SKU-1008 | 投影仪 | 2025-06-30
(8 rows)SKU-1009 与 SKU-1010 的 launched 为 NULL，NULL > DATE '2024-01-01' 结果是 NULL 而非 true，因此被 WHERE 过滤掉。若要包含它们，需显式处理：
sql
SELECT sku, name, launched
FROM products
WHERE launched > DATE '2024-01-01' OR launched IS NULL;IS DISTINCT FROM 系列是唯一"NULL 安全"的比较：
sql
SELECT NULL IS DISTINCT FROM 1 AS a, -- true：NULL 与 1 确实不同
NULL IS DISTINCT FROM NULL AS b, -- false：NULL 与 NULL 视为相同
NULL IS NOT DISTINCT FROM NULL AS c, -- true
1 IS DISTINCT FROM 1 AS d, -- false
coalesce(NULL::int, 0) <> 1 AS e; -- true（用 coalesce 也能做等价判断）text
a | b | c | d | e
---+---+---+---+---
t | f | t | f | t
(1 row)IS DISTINCT FROM 与 <> 的差异可总结为：
| 表达式 | <> 结果 | IS DISTINCT FROM 结果 |
|---|---|---|
1 <> 1 | false | false |
1 <> 2 | true | true |
NULL <> 1 | NULL | true |
NULL <> NULL | NULL | false |
当需要兼容 NULL 的"不同"判断（如数据对比、幂等更新）时，用 IS DISTINCT FROM 能避免三值逻辑的干扰。
实战示例
需求：为商品数据做一次价格与利润分析，步骤 1~6。
步骤 1：计算毛利率（避开整数除法）
sql
SELECT sku, name, price, cost,
round(price - cost, 2) AS gross_profit,
round((price - cost) / price * 100, 2) AS margin_pct
FROM products
WHERE stock > 0
ORDER BY margin_pct DESC;text
sku | name | price | cost | gross_profit | margin_pct
-----------+-------------+---------+----------+--------------+------------
SKU-1002 | USB-C 线缆 | 30.00 | 10.00 | 20.00 | 66.67
SKU-1006 | HDMI 线缆 | 59.00 | 20.00 | 39.00 | 66.10
SKU-1001 | 显示器支架 | 120.00 | 60.00 | 60.00 | 50.00
SKU-1004 | 无线鼠标 | 89.00 | 45.00 | 44.00 | 49.44
SKU-1003 | 机械键盘 | 299.00 | 180.00 | 119.00 | 39.80
SKU-1007 | 扩展坞 | 399.00 | 260.00 | 139.00 | 34.84
SKU-1009 | 摄像头 | 199.00 | 130.00 | 69.00 | 34.67
SKU-1005 | 显示器 | 1200.00 | 900.00 | 300.00 | 25.00
SKU-1008 | 投影仪 | 2999.00 | 2400.00 | 599.00 | 19.97
(9 rows)price 与 cost 都是 numeric，除法不会截断，结果保留小数。若把 price - cost 换成整数列就必须显式转 numeric。
步骤 2：用绝对值统计最大偏差
sql
-- 找出与均值偏差最大的 3 个商品
SELECT sku, name, price,
round(@(price - (SELECT avg(price) FROM products)), 2) AS deviation
FROM products
ORDER BY deviation DESC
LIMIT 3;text
sku | name | price | deviation
-----------+----------+---------+-----------
SKU-1008 | 投影仪 | 2999.00 | 2414.70
SKU-1005 | 显示器 | 1200.00 | 615.70
SKU-1002 | USB-C 线缆 | 30.00 | 554.30
(3 rows)步骤 3：取模做分片
sql
-- 按 id 取模，把商品均匀分到 3 个分片
SELECT sku, name,
abs(hashtext(sku) % 3) AS shard
FROM products
ORDER BY shard, sku;text
sku | name | shard
-----------+-------------+-------
SKU-1002 | USB-C 线缆 | 0
SKU-1006 | HDMI 线缆 | 0
SKU-1010 | 音箱 | 0
SKU-1001 | 显示器支架 | 1
SKU-1003 | 机械键盘 | 1
SKU-1007 | 扩展坞 | 1
SKU-1004 | 无线鼠标 | 2
SKU-1005 | 显示器 | 2
SKU-1008 | 投影仪 | 2
SKU-1009 | 摄像头 | 2
(10 rows)hashtext() 返回整数，可能为负，因此外层用 abs() 取正。
步骤 4：NULL 安全的比较
sql
-- 找出"评分与 4.5 不同"的商品，含 rating 为 NULL 的行
SELECT sku, name, rating
FROM products
WHERE rating IS DISTINCT FROM 4.5
ORDER BY sku;text
sku | name | rating
-----------+-------------+--------
SKU-1002 | USB-C 线缆 | 4.0
SKU-1003 | 机械键盘 | 4.7
SKU-1004 | 无线鼠标 | 4.2
SKU-1005 | 显示器 | 4.8
SKU-1006 | HDMI 线缆 | 3.9
SKU-1007 | 扩展坞 | 4.6
SKU-1008 | 投影仪 | 4.4
SKU-1009 | 摄像头 | 4.9
SKU-1010 | 音箱 | 4.1
(9 rows)对比用 <> 的写法，结果会漏掉 rating IS NULL 的行：
sql
-- 用 <> 会丢失 NULL 行（此处无 NULL 行，故结果相同）
SELECT count(*) FROM products WHERE rating <> 4.5;步骤 5：四舍五入与截断的区别
sql
SELECT round(4.567, 2) AS rounded, -- 四舍五入
trunc(4.567, 2) AS truncated, -- 截断
ceil(4.001) AS ceil_val, -- 向上取整
floor(4.999) AS floor_val, -- 向下取整
round(4.5) AS rnd_half, -- 银行家舍入
round(5.5) AS rnd_half2;text
rounded | truncated | ceil_val | floor_val | rnd_half | rnd_half2
---------+-----------+----------+-----------+----------+-----------
4.57 | 4.56 | 5 | 4 | 4 | 6
(1 row)注意 numeric 的 round 采用"四舍五入"，round(4.5) 返回 4 还是 5 取决于类型（double precision 使用银行家舍入）。
步骤 6：验证库存校验条件
sql
-- 库存为 0 或价格低于成本的异常商品
SELECT sku, name, price, cost, stock
FROM products
WHERE stock <= 0 OR price < cost
ORDER BY sku;text
sku | name | price | cost | stock
-----------+--------+---------+----------+-------
SKU-1010 | 音箱 | 449.00 | 300.00 | 0
(1 row)注意事项
- 整数相除会截断。 只要两个操作数都是整数类型，
/就是不保留小数。金额、比率计算务必转numeric。
sql
-- 错误！7/2 = 3
SELECT 7 / 2;
-- 正确
SELECT 7::numeric / 2;除法分母可能为 0。 用
NULLIF(denominator, 0)把 0 变成NULL，从而让结果自然为NULL而非报错。取模结果符号跟随被除数，
-7 % 3 = -1。做分片时通常需要abs()或((x % n) + n) % n。^是一元优先级高于二元减号的坑。-2 ^ 2得到4而非-4，务必加括号。||遇NULL返回NULL。 想忽略NULL用concat()或coalesce()兜底。<>与!=完全等价，但!=不是 SQL 标准，建议统一用<>。NULL比较返回NULL而非false。 在WHERE中NULL等价于false（行被过滤），但在SELECT输出或CASE判断中NULL是第三种状态。比较时的隐式转换可能让索引失效。 如
varchar_col = 123会触发类型转换，可能无法使用索引。保持两侧类型一致。
sql
-- 不推荐：列被隐式转换，可能无法走索引
SELECT * FROM products WHERE sku = 1001;
-- 推荐
SELECT * FROM products WHERE sku = 'SKU-1001';最佳实践
- 金额与比率一律用
numeric，避免浮点误差与整数截断。
sql
CREATE TABLE orders (
amount numeric(10,2) NOT NULL -- 精确十进制，不用 float4/float8
);- 除法前先处理分母为 0 的情况。
sql
SELECT sku, round(price / NULLIF(cost, 0), 2) AS markup FROM products;- 复杂表达式加括号显式表达意图，不要依赖优先级记忆。
sql
SELECT (a + b) * c, a + (b * c);- 需要 NULL 安全的相等/不等时用
IS DISTINCT FROM。
sql
-- 找出与目标值真正不同的行（含 NULL）
SELECT * FROM products WHERE rating IS DISTINCT FROM 4.5;||拼接前用coalesce()兜底，避免整串结果为NULL。
sql
SELECT coalesce(name, '') || ' (' || coalesce(sku, '') || ')' AS label FROM products;- 比较条件保持两侧类型一致，让优化器能用上索引。
sql
-- 推荐：字面量类型与列类型一致
SELECT * FROM products WHERE launched >= DATE '2025-01-01';- 避免在索引列上做运算，会破坏可索引性（sargability）。
sql
-- 不推荐：列被包裹在函数/运算中，索引失效
SELECT * FROM products WHERE price * 1.1 > 1000;
-- 推荐：把运算移到常量侧
SELECT * FROM products WHERE price > 1000 / 1.1;- 用
EXPLAIN确认表达式是否影响了索引使用，特别是带函数的比较。
sql
EXPLAIN SELECT * FROM products WHERE price > 1000 / 1.1;下一节