这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 本章覆盖 PostgreSQL 日常 SQL：查询、过滤、聚合、连接、集合、插入更新删除和 UPSERT。
3.1 示例表
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status TEXT NOT NULL,
amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
3.2 查询与过滤
SELECT id, name, city
FROM users
WHERE city IN ('Shanghai', 'Beijing')
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;
常见谓词：
| 谓词 | 说明 |
|---|---|
= / <> |
等值 |
BETWEEN |
范围 |
IN |
集合 |
LIKE / ILIKE |
模糊，ILIKE 忽略大小写 |
IS NULL |
空值 |
EXISTS |
子查询存在 |
3.3 聚合
SELECT
u.city,
count(*) AS order_count,
count(DISTINCT o.user_id) AS user_count,
sum(o.amount) AS gmv,
round(avg(o.amount), 2) AS avg_amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID'
GROUP BY u.city
HAVING count(*) > 100
ORDER BY gmv DESC;
过滤顺序：
FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
3.4 连接
SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.city = 'Shanghai';
| 连接 | 语义 |
|---|---|
| INNER JOIN | 只保留匹配行 |
| LEFT JOIN | 左表全保留 |
| RIGHT JOIN | 右表全保留 |
| FULL JOIN | 两表全保留 |
| CROSS JOIN | 笛卡尔积 |
LEFT JOIN 后如果再对右表列使用 WHERE 过滤，可能退化成 INNER JOIN。右表条件通常应放 ON。
3.5 子查询与 CTE
WITH daily AS (
SELECT date_trunc('day', created_at) AS day,
user_id,
sum(amount) AS amount
FROM orders
WHERE status = 'PAID'
GROUP BY 1, 2
)
SELECT day, count(*) AS pay_users, sum(amount) AS gmv
FROM daily
GROUP BY day
ORDER BY day;
PostgreSQL 12+ 默认 materialize 语义可通过 MATERIALIZED / NOT MATERIALIZED 控制。重复引用的 CTE 不一定自动只执行一次，需结合执行计划确认。
3.6 窗口函数
SELECT
user_id,
created_at::date AS day,
amount,
row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn,
sum(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders
WHERE status = 'PAID';
每用户最近一笔订单：
SELECT user_id, amount, created_at
FROM (
SELECT *,
row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE rn = 1;
3.7 插入与更新
INSERT INTO users (name, city) VALUES ('Alice', 'Shanghai');
UPDATE orders
SET status = 'PAID'
WHERE id = 1001;
DELETE FROM orders
WHERE created_at < now() - interval '3 years';
更新前先 SELECT 或 EXPLAIN 确认条件，避免无 WHERE 的大范围误操作。
3.8 UPSERT
INSERT INTO users (id, name, city)
VALUES (1, 'Alice', 'Shanghai')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city;
只插入冲突时忽略：
INSERT INTO users (id, name, city)
VALUES (2, 'Bob', 'Beijing')
ON CONFLICT (id) DO NOTHING;
条件更新：
ON CONFLICT (id)
DO UPDATE SET city = EXCLUDED.city
WHERE users.city IS DISTINCT FROM EXCLUDED.city;
3.9 RETURNING
INSERT INTO users (name, city)
VALUES ('Carol', 'Hangzhou')
RETURNING id, created_at;
DELETE FROM orders
WHERE id = 1002
RETURNING id, amount;
RETURNING 常用于获取自增 ID、审计变更和实现幂等任务。
3.10 空值安全
SELECT
coalesce(nick_name, name, 'unknown') AS display_name,
nullif(phone, '') AS phone,
amount IS DISTINCT FROM 0 AS has_amount
FROM members;
注意：
NULL = NULL结果未知；NOT IN遇 NULL 可能不返回行；- 空字符串不是 NULL；
- 聚合函数通常忽略普通 NULL。
本章小结
PostgreSQL SQL 能力完整，日常重点是把过滤、聚合、连接和窗口函数写清楚。CTE 提升可读性，UPSERT 与 RETURNING 是业务开发高频能力。始终注意空值、连接条件和执行成本。
思考题
- WHERE 和 HAVING 的区别是什么？
- LEFT JOIN 的右表过滤条件应放在哪里？
NOT IN遇 NULL 有什么风险？ON CONFLICT依赖什么约束？- RETURNING 能解决哪些开发问题？