PostgreSQL: PostgreSQL运算符与表达式
最后更新：2026-08-26
1. 你将学到
- 算术运算符
+ - * / %及其溢出与精度行为 - 比较运算符与逻辑运算符
- 字符串连接
||与模式匹配操作符 - 类型转换
::和CAST() - 行构造器
ROW()与区间操作符@> <@ &< &> - - 子查询表达式
IN / EXISTS / ANY / ALL
2. 故事
Charlie 是 SaaS 平台的财务分析师，负责：
- 计算每笔订单的税后金额（
total * 1.08） - 判断订单是否落在指定日期区间内
- 将字符串格式的日期转换为 DATE 类型进行比较
- 检查价格区间是否有重叠
这些任务需要他精通 PostgreSQL 的各类运算符与表达式。
3. Concept：算术运算符
(1) 基本算术运算符
| 运算符 | 含义 | 示例 | 结果 |
|---|---|---|---|
+ |
加 | 100 + 8 |
108 |
- |
减 | 500 - 50 |
450 |
* |
乘 | 29.99 * 3 |
89.97 |
/ |
整数除（截断） | 7 / 2 |
3 |
/ |
数值除（含小数） | 7.0 / 2 |
3.5 |
% |
取模 | 10 % 3 |
1 |
^ |
幂运算 | 2 ^ 10 |
1024 |
| ` | /` | 平方根 | ` |
| ` | /` | 立方根 | |
! |
阶乘 | 5 ! |
120 |
@ |
绝对值 | @ -15 |
15 |
▶ 示例：计算税后金额
SQL
SELECT
order_id,
total_amount,
total_amount * 0.08 AS tax,
total_amount * 1.08 AS total_with_tax
FROM orders
WHERE order_status = 'completed';
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ 示例：整数除法 vs 数值除法
SQL
SELECT
7 / 2 AS int_div,
7.0 / 2 AS numeric_div,
7 / 2.0 AS numeric_div2,
CAST(7 AS NUMERIC) / 2 AS cast_div;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) 运算符优先级
| 优先级 | 运算符 | 说明 |
|---|---|---|
| 1（最高） | . |
成员访问 |
| 2 | :: |
类型转换 |
| 3 | [ |
数组下标 |
| 4 | -（一元）、@、! |
一元运算 |
| 5 | ^ |
幂 |
| 6 | *、/、% |
乘除取模 |
| 7 | +、- |
加减 |
| 8 | ` | |
| 9 | 比较运算符 | = <> < > <= >= |
| 10 | IS、IN、BETWEEN |
谓词 |
| 11 | NOT |
逻辑非 |
| 12 | AND |
逻辑与 |
| 13（最低） | OR |
逻辑或 |
▶ 示例：优先级与括号
SQL
SELECT
2 + 3 * 4 AS no_parens,
(2 + 3) * 4 AS with_parens;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) 溢出与精度
PostgreSQL 的 INTEGER 范围约 -2.1 billion 到 2.1 billion，超出会报错。建议金额计算使用 NUMERIC(precision, scale) 或 DECIMAL。
| 类型 | 范围 | 精度 | 适用场景 |
|---|---|---|---|
SMALLINT |
-32768 ~ 32767 | 整数 | 小范围计数 |
INTEGER |
±2.1 billion | 整数 | 通用整数 |
BIGINT |
±9.2 quintillion | 整数 | 大范围 ID |
NUMERIC(p,s) |
无限制 | 任意精度 | 金额计算 |
▶ 示例：金额精度安全计算
SQL
SELECT
order_id,
total_amount::NUMERIC(12,2) AS amount,
(total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
(total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
4. Concept：比较运算符
(1) 基本比较
| 运算符 | 含义 | 1 <> 2 |
NULL = NULL |
|---|---|---|---|
= |
等于 | FALSE | NULL |
<> / != |
不等于 | TRUE | NULL |
< |
小于 | TRUE | NULL |
> |
大于 | FALSE | NULL |
<= |
小于等于 | TRUE | NULL |
>= |
大于等于 | FALSE | NULL |
任何与 NULL 的比较结果都是 NULL，不是 TRUE 或 FALSE。
▶ 示例：比较运算
SQL
SELECT product_name, unit_price
FROM products
WHERE unit_price >= 100
AND unit_price < 500
AND category = 'Electronics';
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) BETWEEN 与复合比较
| 表达式 | 等价 |
|---|---|
x BETWEEN a AND b |
x >= a AND x <= b |
x NOT BETWEEN a AND b |
x < a OR x > b |
▶ 示例：BETWEEN 范围比较
SQL
SELECT order_id, total_amount, order_date
FROM orders
WHERE total_amount BETWEEN 100 AND 1000
AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) IS DISTINCT FROM
IS DISTINCT FROM 将 NULL 视为一个可比较的值，避免了标准比较的 NULL 陷阱。
| 表达式 | NULL = NULL |
NULL IS DISTINCT FROM NULL |
|---|---|---|
| 结果 | NULL | FALSE |
1 IS DISTINCT FROM NULL |
— | TRUE |
▶ 示例：IS DISTINCT FROM
SQL
SELECT order_id, old_status, new_status
FROM order_status_log
WHERE old_status IS DISTINCT FROM new_status;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
5. Concept：逻辑运算符
(1) 三值逻辑真值表
PostgreSQL 使用三值逻辑（TRUE / FALSE / NULL），NULL 参与逻辑运算时结果可能不符合直觉。
| a | b | a AND b | a OR b |
|---|---|---|---|
| T | T | T | T |
| T | F | F | T |
| T | N | N | T |
| F | T | F | T |
| F | F | F | F |
| F | N | F | N |
| N | T | N | T |
| N | F | F | N |
| N | N | N | N |
▶ 示例：NULL 参与逻辑运算
SQL
SELECT
TRUE AND NULL AS and_result,
TRUE OR NULL AS or_result,
FALSE AND NULL AS and_false,
FALSE OR NULL AS or_null,
NOT NULL AS not_result;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ 示例：WHERE 中 NULL 的行为
SQL
-- This excludes rows where discount_rate is NULL
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0;
-- NULL rows are NOT included because NULL > 0 is NULL, NULL = 0 is NULL
-- Fix: explicitly handle NULL
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0 OR discount_rate IS NULL;
输出：
TEXT
📖 仅展示
count
-------
5
(1 row)
6. Concept：字符串运算符
(1) 字符串连接 ||
|| 是 PostgreSQL 的字符串连接运算符，与 SQL Server 的 + 不同。
| 运算符 | 含义 | 示例 |
|---|---|---|
| ` | ` |
▶ 示例：字符串连接
SQL
SELECT
customer_id,
first_name || ' ' || last_name AS full_name,
'Order #' || order_id || ': $' || total_amount::TEXT AS order_summary
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
输出：
TEXT
📖 仅展示
result
----------
42.50
(1 row)
(2) 字符串与 NULL 的连接
任何字符串与 NULL 连接结果都是 NULL，需用 COALESCE 处理。
| 表达式 | 结果 |
|---|---|
| `'A' | |
| `'A' |
▶ 示例：安全字符串连接
SQL
SELECT
product_name || COALESCE(' - ' || description, '') AS product_info
FROM products;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
7. Concept：类型转换
(1) :: 与 CAST() 两种写法
| 写法 | 示例 | 标准SQL | 说明 |
|---|---|---|---|
::type |
'2025-01-01'::DATE |
否（PG 独有） | 简洁，推荐在 PG 中使用 |
CAST(expr AS type) |
CAST('2025-01-01' AS DATE) |
是 | 可移植，冗长 |
▶ 示例：字符串转日期
SQL
SELECT
order_id,
order_date::DATE AS date_only,
'2025-06-15'::DATE AS literal_date,
CAST('2025-06-15 14:30:00' AS TIMESTAMP) AS full_timestamp
FROM orders
WHERE order_date::DATE >= '2025-01-01'::DATE;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ 示例：数值类型转换
SQL
SELECT
total_amount::NUMERIC(12,2) AS rounded,
total_amount::INTEGER AS truncated,
total_amount::TEXT AS as_text
FROM orders;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) 常见转换场景
| 源类型 | 目标类型 | 用法 |
|---|---|---|
| TEXT → DATE | '2025-01-01'::DATE |
日期比较 |
| TEXT → TIMESTAMP | CAST(ts AS TIMESTAMP) |
时间戳比较 |
| NUMERIC → TEXT | 123.45::TEXT |
字符串连接 |
| INTEGER → NUMERIC | id::NUMERIC |
精度计算 |
| TEXT → INTEGER | '42'::INTEGER |
数值运算 |
▶ 示例：Charlie 的日期范围检查
SQL
SELECT order_id, total_amount, order_date
FROM orders
WHERE order_date >= '2025-01-01'::DATE
AND order_date < '2025-07-01'::DATE
AND total_amount::NUMERIC(12,2) > 500;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
8. Concept：行构造器与子查询表达式
(1) ROW 构造器
ROW(val1, val2, ...) 构造一个匿名行记录，可用于行级比较。
▶ 示例：行构造器比较
SQL
SELECT order_id, order_status, total_amount
FROM orders
WHERE ROW(order_status, total_amount) = ROW('completed', 500);
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) 行比较与 IN
行构造器可与 IN 搭配做多列匹配。
▶ 示例：多列 IN 匹配
SQL
SELECT order_id, customer_id, order_status
FROM orders
WHERE (order_status, customer_id) IN (
('completed', 1001),
('completed', 1002),
('pending', 1003)
);
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) 子查询表达式对比
| 表达式 | 语义 | 返回类型 |
|---|---|---|
col IN (subquery) |
等于子查询结果中任一值 | BOOLEAN |
col = ANY(subquery) |
同 IN | BOOLEAN |
col > ALL(subquery) |
大于子查询所有值 | BOOLEAN |
EXISTS (subquery) |
子查询是否有结果 | BOOLEAN |
▶ 示例：ALL 比较子查询
SQL
SELECT product_name, unit_price, category
FROM products
WHERE unit_price > ALL (
SELECT AVG(unit_price) FROM products GROUP BY category
);
输出：
TEXT
📖 仅展示
result
----------
42.50
(1 row)
9. Concept：区间操作符
(1) Range 类型
PostgreSQL 内置区间类型 int4range、int8range、numrange、daterange、tstzrange 等。
▶ 示例：创建 daterange
SQL
SELECT daterange('2025-01-01'::DATE, '2025-06-30'::DATE) AS h1_range;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) 区间操作符一览
| 操作符 | 含义 | 示例 |
|---|---|---|
@> |
包含元素 | '[1,5]'::int4range @> 3 → TRUE |
<@ |
被包含 | 3 <@ '[1,5]'::int4range → TRUE |
&& |
重叠 | '[1,5]'::int4range && '[3,8]'::int4range → TRUE |
&< |
不延伸到右侧 | '[1,3]'::int4range &< '[2,5]'::int4range → TRUE |
&> |
不延伸到左侧 | '[3,5]'::int4range &> '[1,3]'::int4range → TRUE |
| `- | -` | 相邻 |
+ |
并集 | '[1,3]'::int4range + '[3,6]'::int4range → [1,6) |
* |
交集 | '[1,5]'::int4range * '[3,8]'::int4range → [3,5) |
- |
差集 | '[1,5]'::int4range - '[3,8]'::int4range → [1,3) |
▶ 示例：检查日期区间重叠
SQL
SELECT o1.order_id, o2.order_id
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id
AND o1.order_id < o2.order_id
AND daterange(o1.order_date, o1.delivery_date) &&
daterange(o2.order_date, o2.delivery_date);
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ 示例：价格区间包含检查
SQL
SELECT product_name, unit_price
FROM products
WHERE numrange(100, 500) @> unit_price::NUMERIC;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
10. 流程图：类型转换决策
flowchart TD
A[需要类型转换?] --> B{目标类型?}
B -->|日期/时间| C["::DATE / ::TIMESTAMP"]
B -->|数值| D["::NUMERIC / ::INTEGER"]
B -->|字符串| E["::TEXT"]
B -->|可移植性要求| F["CAST(expr AS type)"]
C --> G{源含格式?}
G -->|标准格式| H[直接 :: 转换]
G -->|自定义格式| I["TO_DATE() / TO_TIMESTAMP()"]
D --> J[注意精度丢失]
E --> K[注意 NULL 连接]
H --> L[完成]
I --> L
J --> L
K --> L
F --> L
style A fill:#e1f5fe
style L fill:#c8e6c9
11. 综合示例
Charlie 完成了财务分析所需的各类运算。
SQL
-- Step 1: Tax calculation with precision control
SELECT
order_id,
total_amount::NUMERIC(12,2) AS amount,
(total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
(total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders
WHERE order_status = 'completed'
AND order_date >= '2025-01-01'::DATE;
-- Step 2: Date range check with daterange
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE daterange('2025-01-01'::DATE, '2025-07-01'::DATE) @> order_date::DATE
AND order_status <> 'cancelled';
-- Step 3: String-to-date conversion and formatting
SELECT
order_id,
'Order #' || order_id || ' - ' ||
COALESCE(customer_name, 'Unknown') ||
' ($' || total_amount::TEXT || ')' AS order_label
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date::DATE >= '2025-01-01'::DATE
ORDER BY o.total_amount DESC;
-- Step 4: Row constructor and range overlap
SELECT
p1.product_name AS product_a,
p2.product_name AS product_b,
numrange(p1.unit_price, p1.unit_price * 1.2) AS price_range_a,
numrange(p2.unit_price, p2.unit_price * 1.2) AS price_range_b
FROM products p1
JOIN products p2 ON p1.product_id < p2.product_id
WHERE p1.category = p2.category
AND numrange(p1.unit_price, p1.unit_price * 1.2) &&
numrange(p2.unit_price, p2.unit_price * 1.2)
AND p1.is_active = true AND p2.is_active = true;
❓ 常见问题
Q 整数除法 7/2 返回 3 还是 3.5？
A 返回 3。PostgreSQL 中两个整数相除结果截断为整数。要得到 3.5，需至少一方为小数类型：7.0/2 或 7::NUMERIC/2。
Q :: 和 CAST() 哪个更好？
A :: 更简洁，是 PostgreSQL 特有语法；CAST() 是 SQL 标准写法。纯 PostgreSQL 项目推荐 ::，需要跨数据库兼容时用 CAST()。
Q NULL 参与逻辑运算有什么陷阱？
A TRUE AND NULL = NULL（非 TRUE），FALSE OR NULL = NULL（非 FALSE），NOT NULL = NULL。在 WHERE 中 NULL 被视为非 TRUE，相关行被排除。
Q 字符串与 NULL 连接为什么结果是 NULL？
A SQL 标准规定任何值与 NULL 的字符串连接结果为 NULL。用 COALESCE(col, '') 替换 NULL 再连接。
Q 行构造器比较什么时候有用？
A 当需要同时比较多列组合时，如 WHERE (status, amount) = ROW('completed', 500)，比分别比较两列更简洁，也可用于多列 IN。
Q 区间操作符只能用于内置 Range 类型吗？
A 是的，@> <@ && 等操作符作用于 PostgreSQL 内置的 Range 类型（int4range, numrange, daterange 等）。普通列需要先构造 Range 值再比较。
Q 幂运算 ^ 的优先级比乘除高吗？
A 是的，^ 优先级高于 * / %。2^3^2 = 2^(3^2) = 512（右结合），不是 (2^3)^2 = 64。
Q IS DISTINCT FROM 和 <> 有什么区别？
A <> 比较中 NULL <> NULL 返回 NULL（不是 TRUE 也不是 FALSE），IS DISTINCT FROM 把 NULL 当作可比较值，NULL IS DISTINCT FROM NULL 返回 FALSE，1 IS DISTINCT FROM NULL 返回 TRUE。
📖 小节
- 算术运算符注意整数除法截断与精度，金额计算用
NUMERIC(p,s) - 比较运算符与
NULL比较结果为NULL，用IS DISTINCT FROM做安全比较 - 三值逻辑中
NULL AND TRUE = NULL、NULL OR FALSE = NULL - 字符串连接
||遇NULL结果为NULL，用COALESCE处理 ::是 PG 独有类型转换语法，CAST()是 SQL 标准- 行构造器
ROW()支持多列比较与多列 IN - 区间操作符
@> <@ &&提供高效的 Range 包含与重叠判断
📝 作业
-
⭐ 编写查询，计算
order_items表中每行的unit_price * quantity，将结果转换为NUMERIC(10,2)，并按行金额降序排列。 -
⭐⭐ 编写查询，用
daterange和@>操作符查找order_date落在 2025 年 Q1（1月1日至3月31日）的订单，同时用||连接生成订单摘要字符串（格式："Order #id - customer_name - $amount"），处理 NULL 客户名。 -
⭐⭐⭐ 编写查询，找出同一分类下价格区间重叠的商品对（用
numrange(unit_price, unit_price * 1.2)构造区间，用&&判断重叠），并展示每个商品对的行构造器比较结果ROW(a.price, a.category) = ROW(b.price, b.category)是否相等。
