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 函数性能对比？回复 性能对比

随时告诉我！

PostgreSQL 表达式

PostgreSQL 表达式（Expressions）完全指南

一、什么是表达式？

二、表达式分类

三、核心语法与示例

1. 算术表达式

2. 字符串表达式

3. 条件表达式（CASE）

4. NULL 处理表达式

5. 日期时间表达式

6. JSON 表达式

7. 数组表达式

8. 子查询表达式（标量子查询）

9. 类型转换表达式

四、表达式在各语句中的使用

五、生成列（Generated Columns）—— 表达式持久化

六、表达式索引（Expression Index）—— 性能提升

七、常见函数 + 表达式组合

八、表达式性能优化

九、表达式在视图中的应用

十、表达式实战：复杂业务逻辑

十一、表达式错误与调试

十二、最佳实践 Checklist

十三、一键生成 100 条测试数据（含表达式）

十四、快速上手：5 分钟掌握表达式

PostgreSQL LIMIT 子句

PostgreSQL 创建表格

PostgreSQL pgAdmin 工具

PostgreSQL AUTO INCREMENT（自动增长）

PostgreSQL 索引

PostgreSQL 触发器

发表回复取消回复

PostgreSQL 表达式（Expressions）完全指南

一、什么是表达式？

二、表达式分类

三、核心语法与示例

1. 算术表达式

2. 字符串表达式

3. 条件表达式（CASE）

4. NULL 处理表达式

5. 日期时间表达式

6. JSON 表达式

7. 数组表达式

8. 子查询表达式（标量子查询）

9. 类型转换表达式

四、表达式在各语句中的使用

五、生成列（Generated Columns）—— 表达式持久化

六、表达式索引（Expression Index）—— 性能提升

七、常见函数 + 表达式组合

八、表达式性能优化

九、表达式在视图中的应用

十、表达式实战：复杂业务逻辑

十一、表达式错误与调试

十二、最佳实践 Checklist

十三、一键生成 100 条测试数据（含表达式）

十四、快速上手：5 分钟掌握表达式

类似文章

发表回复 取消回复

发表回复取消回复