PostgreSQL 表达式
PostgreSQL 表达式（Expressions）完全指南
表达式 是 PostgreSQL 中 可以计算出值的任何组合，包括常量、列名、函数、运算符、子查询等。
它是SELECT、WHERE、ORDER BY、UPDATE、INSERT等语句的核心。
一、什么是表达式？
-- 这些都是表达式
5 + 3
UPPER(username)
age > 18
now() - created_at
(SELECT COUNT(*) FROM orders WHERE user_id = u.id)表达式 = 可求值的单元，最终返回 标量值（单个值）
二、表达式分类
| 类型 | 示例 | 说明 |
|---|---|---|
| 常量表达式 | 'hello'、100、true | 固定值 |
| 列引用 | username、salary * 1.1 | 表中的列 |
| 算术表达式 | price * qty | + - * / % ^ |
| 函数调用 | now()、COALESCE(email, 'N/A') | 内置或自定义函数 |
| 条件表达式 | CASE WHEN ... THEN ... END | 逻辑判断 |
| 子查询表达式 | (SELECT MAX(salary) FROM users) | 标量子查询 |
| 类型转换 | age::TEXT | :: 或 CAST() |
| 集合表达式 | ARRAY[1,2,3] | 数组构造 |
三、核心语法与示例
1. 算术表达式
SELECT
salary,
salary * 1.1 AS bonus_10,
salary / 12 AS monthly,
salary % 1000 AS remainder
FROM users;2. 字符串表达式
SELECT
username,
UPPER(username) AS shout,
'User: ' || username || ' (ID: ' || id || ')' AS label,
LENGTH(username) AS name_len
FROM users;3. 条件表达式（CASE）
SELECT
username,
salary,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level,
CASE WHEN is_active THEN 'Active' ELSE 'Inactive' END AS status
FROM users;4. NULL 处理表达式
SELECT
email,
COALESCE(email, 'No email') AS safe_email,
NULLIF(username, 'admin') AS non_admin -- 如果是 admin 返回 NULL
FROM users;5. 日期时间表达式
SELECT
created_at,
now() - created_at AS age,
DATE_TRUNC('day', created_at) AS day,
EXTRACT(YEAR FROM created_at) AS year,
created_at + INTERVAL '30 days' AS future
FROM users;6. JSON 表达式
SELECT
profile,
profile -> 'settings' ->> 'theme' AS theme,
profile @> '{"level": 5}' AS is_senior,
jsonb_build_object('name', username, 'dept', department) AS info
FROM users;7. 数组表达式
SELECT
tags,
tags[1] AS first_tag,
ARRAY_LENGTH(tags, 1) AS tag_count,
tags || '{"new"}' AS added,
ARRAY_AGG(username) OVER (PARTITION BY department) AS dept_users
FROM users;8. 子查询表达式（标量子查询）
SELECT
username,
salary,
(SELECT AVG(salary) FROM users) AS global_avg,
salary > (SELECT AVG(salary) FROM users WHERE department = u.department) AS above_dept_avg
FROM users u;9. 类型转换表达式
SELECT
id::TEXT || '-' || username AS code,
CAST(salary AS INTEGER) AS int_salary,
age::TEXT AS age_str
FROM users;四、表达式在各语句中的使用
| 语句 | 表达式位置 | 示例 |
|---|---|---|
SELECT | 选择列表 | SELECT salary * 1.1 |
WHERE | 条件 | WHERE age > 18 AND UPPER(username) LIKE 'A%' |
ORDER BY | 排序 | ORDER BY salary DESC, LENGTH(username) |
UPDATE | 赋值 | UPDATE users SET salary = salary * 1.05 |
INSERT | 值 | INSERT INTO logs VALUES (now(), 'login') |
HAVING | 分组过滤 | HAVING COUNT(*) > 5 |
GROUP BY | 分组键 | GROUP BY DATE_TRUNC('month', created_at) |
五、生成列（Generated Columns）—— 表达式持久化
ALTER TABLE users
ADD COLUMN monthly_salary NUMERIC
GENERATED ALWAYS AS (salary / 12) STORED;
-- 查询
SELECT username, monthly_salary FROM users;类型：
STORED：物理存储，可索引VIRTUAL：计算时生成（PG 不支持）
六、表达式索引（Expression Index）—— 性能提升
-- 加速忽略大小写搜索
CREATE INDEX idx_users_lower_name ON users(LOWER(username));
-- 加速 JSON 查询
CREATE INDEX idx_users_level ON users((profile ->> 'level'));
-- 加速函数查询
CREATE INDEX idx_users_year ON users(EXTRACT(YEAR FROM created_at));七、常见函数 + 表达式组合
| 函数 | 用途 | 示例 |
|---|---|---|
COALESCE(a,b,c) | 第一个非 NULL | COALESCE(email, phone, 'N/A') |
NULLIF(a,b) | a=b 返回 NULL | NULLIF(score, 0) |
GREATEST(a,b) | 最大值 | GREATEST(salary, bonus) |
LEAST(a,b) | 最小值 | LEAST(price1, price2) |
GENERATE_SERIES() | 生成序列 | SELECT GENERATE_SERIES(1,10) |
ROW_NUMBER() OVER (...) | 窗口函数 | ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) |
八、表达式性能优化
| 技巧 | 说明 |
|---|---|
| 避免在 WHERE 中用函数包裹列 | 错：WHERE UPPER(name) = 'ALICE' → 无法用索引对： WHERE name = 'alice' + COLLATE 或表达式索引 |
| 使用表达式索引 | CREATE INDEX ON users(LOWER(email)); |
| 子查询改 JOIN | 标量子查询有时慢 |
| 避免重复计算 | 用 CTE 或 WITH |
-- 慢
SELECT * FROM users WHERE LENGTH(username) > 10;
-- 快：建索引
CREATE INDEX idx_users_name_len ON users(LENGTH(username));九、表达式在视图中的应用
CREATE VIEW user_summary AS
SELECT
username,
salary,
CASE WHEN salary > 80000 THEN 'High' ELSE 'Normal' END AS level,
COALESCE(email, 'No email') AS contact
FROM users;
-- 查询视图
SELECT * FROM user_summary WHERE level = 'High';十、表达式实战：复杂业务逻辑
-- 1. 用户等级 + 奖励
SELECT
username,
salary,
CASE
WHEN salary >= 100000 THEN 'S'
WHEN salary >= 70000 THEN 'A'
WHEN salary >= 50000 THEN 'B'
ELSE 'C'
END AS grade,
salary * CASE
WHEN EXTRACT(MONTH FROM now()) = 12 THEN 2.0 -- 双薪
ELSE 1.1
END AS bonus
FROM users;
-- 2. 动态 JSON 构建
SELECT
username,
jsonb_build_object(
'id', id,
'name', username,
'active', is_active,
'tags', tags
) AS user_info
FROM users;十一、表达式错误与调试
| 错误 | 原因 | 解决 |
|---|---|---|
operator does not exist: text + integer | 类型不匹配 | username || id::TEXT |
function upper(boolean) does not exist | 函数参数错 | UPPER(username::TEXT) |
division by zero | 除数为 0 | NULLIF(denominator, 0) |
subquery returns more than one row | 标量子查询返回多行 | 用 LIMIT 1 或聚合 |
十二、最佳实践 Checklist
| 项目 | 建议 |
|---|---|
| 类型一致 | 避免隐式转换 |
| NULL 安全 | 用 COALESCE、NULLIF |
| 可读性 | 复杂表达式用 () |
| 性能 | 避免在列上用函数 |
| 索引 | 常用表达式建索引 |
| 生成列 | 频繁计算字段用 GENERATED ALWAYS |
十三、一键生成 100 条测试数据（含表达式）
INSERT INTO users (username, email, age, salary, department, is_active)
SELECT
'user_' || n,
'user_' || n || '@test.com',
(random() * 50 + 18)::INT,
(random() * 100000 + 30000)::NUMERIC(10,2),
CASE WHEN n % 3 = 0 THEN 'IT' ELSE 'HR' END,
n % 5 != 0
FROM generate_series(1, 100) AS n;十四、快速上手：5 分钟掌握表达式
-- 1. 算术 + 字符串
SELECT username, salary * 1.1 AS bonus, 'Bonus: $' || (salary * 0.1) FROM users;
-- 2. 条件 + NULL
SELECT COALESCE(email, phone, 'N/A'),
CASE WHEN is_active THEN 'Yes' ELSE 'No' END
FROM users;
-- 3. JSON + 数组
SELECT profile ->> 'level', tags[1] FROM users;
-- 4. 子查询 + 窗口
SELECT username, salary,
(SELECT AVG(salary) FROM users) AS avg,
RANK() OVER (ORDER BY salary DESC)
FROM users;现在就动手：
在 users 表中：
- 查询
salary * 1.1且username以a开头（忽略大小写）的用户 - 用
CASE给age < 30的用户打上Young标签 - 查询
profile中level > 5且tags包含dev的用户 - 添加生成列
year_of_birth = EXTRACT(YEAR FROM now()) - age
需要我生成：
- 1000 条复杂测试数据（含 JSON/数组）？回复
测试数据 - 动态分区表达式建表示例？回复
分区表达式 - Python 中使用表达式查询？回复
Python 表达式 - 表达式 vs 函数性能对比？回复
性能对比
随时告诉我！