PostgreSQL 学习资料 · 入门 / 练习 / 精通 / 扩展

原创已于 2026-08-02 14:46:55 修改 · 186 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

于 2026-08-02 14:46:03 首次发布

PostgreSQL 学习资料 · 入门 / 练习 / 精通 / 扩展

入门学习资料

1. PostgreSQL 简介

PostgreSQL 是一个免费的对象-关系型数据库服务器（ORDBMS），在灵活的 BSD 许可证下发行。它的 Slogan 是 “世界上最先进的开源关系型数据库”，开发者通常把它读作 post-gress-Q-L。

为什么选择 PostgreSQL

开源且自由：BSD 许可，可自由修改与商用，无厂商锁定。
标准兼容：高度符合 SQL 标准，支持复杂查询、窗口函数、CTE 等。
可扩展：支持自定义类型、函数、操作符、索引方法，以及丰富的扩展插件（如 PostGIS、pgvector）。
可靠的事务：基于 MVCC（多版本并发控制）实现高并发下的数据一致性。
NoSQL 能力：原生支持 JSON / JSONB、XML、数组、hstore 等半结构化数据。

ORDBMS 关键术语

术语	说明
数据库 (Database)	一些关联表的集合
表 (Table)	数据的矩阵，看起来像电子表格
列 (Column)	同一类数据，如“用户名”
行 (Row)	一条记录 / 元组
主键 (Primary Key)	唯一标识一行，全表仅一个
外键 (Foreign Key)	建立两表之间的关联
索引 (Index)	类似书籍目录，加速查询
参照完整性	不允许引用不存在的实体，保证数据一致

2. 安装与环境配置

Linux (Ubuntu / Debian)

# 安装
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# 启动并设置开机自启
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 查看版本
psql --version

macOS

# 使用 Homebrew
brew install postgresql@16
brew services start postgresql@16

Windows

推荐下载官方 EnterpriseDB 安装包（postgresql.org/download），按向导安装并勾选 pgAdmin 与 Stack Builder。安装完成后，可通过“开始菜单 → pgAdmin 4”图形化管理，或用命令行 psql。

使用 psql 连接

# 切换到 postgres 系统用户后连接
sudo -u postgres psql

# 或指定用户/主机/库连接
psql -U myuser -h localhost -d mydb -p 5432

# 常用元命令
\?   -- 帮助
\l   -- 列出数据库
\dt  -- 列出当前库的表
\du  -- 列出用户角色
\q   -- 退出

提示：第一次安装后，建议为 postgres 超级用户设置密码：ALTER USER postgres PASSWORD 'yourpassword';，并创建专属业务用户而非直接使用超级用户。

3. 核心概念

Cluster（数据库集簇）：一个 PostgreSQL 实例（服务进程）管理的一组数据库，由同一个数据目录组成。
Database（数据库）：相互隔离的逻辑命名空间，库与库之间默认不能直接互访（需用 postgres_fdw 之类的外部包装器）。
Schema（模式）：数据库内部的命名空间，用于分组表、视图等对象。默认存在 public schema。可把不同业务模块放在不同 schema 下避免命名冲突。
Tablespace（表空间）：对象在磁盘上的物理存储位置，可用于把热点表放到更快的磁盘。

-- 创建并使用 schema
CREATE SCHEMA shop;
SET search_path TO shop, public;

4. 数据类型

分类	类型	说明
数值	smallint / integer / bigint	2 / 4 / 8 字节整数
数值	decimal / numeric	任意精度，适合金额
数值	real / double precision	4 / 8 字节浮点
数值	serial / bigserial	自增整数（伪类型）
字符	char(n) / varchar(n) / text	定长 / 变长 / 不限长文本
时间	timestamp / date / time / interval	日期时间 / 日期 / 时间 / 时间间隔
布尔	boolean	true / false / null
枚举	enum	自定义取值集合
JSON	json / jsonb	文本 / 二进制存储（推荐 jsonb）
其他	uuid / array / bytea / 几何类型	唯一标识 / 数组 / 二进制 / 点线面

注意：金钱字段永远不要用 float，请用 numeric(12,2) 之类精确类型。jsonb 会解析并去重键、支持索引，绝大多数场景优于 json。

5. 数据库与表操作 (DDL)

-- 创建数据库
CREATE DATABASE shopdb OWNER myuser;

-- 删除数据库
DROP DATABASE IF EXISTS shopdb;

-- 创建表
CREATE TABLE users (
  id        serial PRIMARY KEY,
  username  varchar(50) NOT NULL,
  email     varchar(120) UNIQUE,
  age       smallint CHECK (age >= 0),
  created_at timestamp DEFAULT now()
);

-- 修改表：增加列
ALTER TABLE users ADD COLUMN phone varchar(20);

-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE bigint USING phone::bigint;

-- 删除表
DROP TABLE IF EXISTS users CASCADE;

-- 清空表（保留结构）
TRUNCATE TABLE users;

6. 增删改查 CRUD

-- INSERT
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@x.com', 28);

-- 返回插入的行（便于拿到自增 id）
INSERT INTO users (username, email) VALUES ('bob', 'bob@x.com')
RETURNING id;

-- SELECT
SELECT id, username, email FROM users;
SELECT * FROM users WHERE id = 1;

-- UPDATE
UPDATE users SET age = 29 WHERE username = 'alice';

-- DELETE
DELETE FROM users WHERE id = 2;

警告：生产环境中 UPDATE / DELETE 务必带 WHERE，否则会全表更新/删除。可先用 SELECT 验证条件。

7. 查询进阶

-- 条件组合
SELECT * FROM users
WHERE age >= 18 AND age <= 40
  AND (username LIKE 'a%' OR email LIKE '%@x.com');

-- IN / BETWEEN / IS NULL
SELECT * FROM users WHERE age IN (18, 21, 30);
SELECT * FROM users WHERE age BETWEEN 18 AND 40;
SELECT * FROM users WHERE phone IS NULL;

-- 排序、去重、限制
SELECT DISTINCT age FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- 聚合与分组
SELECT age, count(*) FROM users GROUP BY age HAVING count(*) > 1;

-- 别名
SELECT username AS 用户名, age AS 年龄 FROM users u;

8. 多表连接 JOIN

-- 内连接：只返回两表匹配的行
SELECT u.username, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

-- 左连接：保留左表全部行
SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- 自连接与多表连接
SELECT a.username, b.username AS referrer
FROM users a JOIN users b ON a.refer_id = b.id;

-- UNION：合并两个查询结果（去重）
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

连接类型	结果
INNER JOIN	两表都匹配
LEFT JOIN	左表全 + 右表匹配
RIGHT JOIN	右表全 + 左表匹配
FULL JOIN	两表并集
CROSS JOIN	笛卡尔积

9. 约束 (Constraints)

CREATE TABLE orders (
  id        serial PRIMARY KEY,
  user_id   integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code      varchar(20) UNIQUE,
  amount    numeric(10,2) CHECK (amount > 0),
  status    varchar(10) DEFAULT 'pending',
  CONSTRAINT chk_status CHECK (status IN ('pending','paid','done'))
);

PRIMARY KEY = NOT NULL + UNIQUE，每张表一个。
FOREIGN KEY：引用其他表，配合 ON DELETE/UPDATE 行为保证参照完整性。
UNIQUE：列值不重复（允许多个 NULL）。
CHECK：自定义校验表达式。
NOT NULL / DEFAULT：非空与默认值。

10. 索引 (Index)

-- 普通 B-tree 索引（默认）
CREATE INDEX idx_users_email ON users(email);

-- 多列（复合）索引
CREATE INDEX idx_users_age_name ON users(age, username);

-- 唯一索引
CREATE UNIQUE INDEX idx_uniq_email ON users(email);

-- 查看 / 删除索引
DROP INDEX IF EXISTS idx_users_email;

注意：索引加速读、拖慢写（INSERT/UPDATE/DELETE 需同步维护）。不要盲目建索引，应针对高频 WHERE、JOIN、ORDER BY 列建立，并用 EXPLAIN 验证是否被使用。

11. 视图 (View)

-- 视图是保存的查询，像表一样使用
CREATE VIEW vip_users AS
SELECT id, username FROM users WHERE age >= 30;

SELECT * FROM vip_users;

DROP VIEW IF EXISTS vip_users;

视图不存储数据（仅保存定义），用于封装复杂查询、做权限隔离。需要落盘结果时用物化视图（见精通章节）。

12. 函数与操作符

-- 字符串
SELECT upper(username), concat(first, ' ', last), length(email) FROM users;

-- 聚合
SELECT avg(amount), sum(amount), max(created_at) FROM orders;

-- 时间与条件
SELECT now(), date_trunc('month', created_at),
       coalesce(phone, '无') FROM users;

-- 条件表达式
SELECT username,
  CASE WHEN age < 18 THEN '未成年' ELSE '成年' END AS 阶段
FROM users;

13. 事务 (Transaction)

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 两个更新同时生效
-- ROLLBACK;  -- 出错时回滚

事务满足 ACID：原子性、一致性、隔离性、持久性。PostgreSQL 默认每条语句自动提交；多语句需显式 BEGIN ... COMMIT 包裹。

学习练习

练习说明

以下练习建议按顺序完成。先建库建表，再逐步练习 CRUD、查询、聚合、连接。每道题都给出了建表与示例数据脚本，参考答案点击「显示答案」展开。配套一个 school 示例库，包含 students、courses、enrolls 三张表。

-- 初始化练习库
CREATE DATABASE school;
\c school

CREATE TABLE students (
  id    serial PRIMARY KEY,
  name  varchar(40) NOT NULL,
  gender char(1) CHECK (gender IN ('M','F')),
  age   smallint,
  city  varchar(30)
);

CREATE TABLE courses (
  id    serial PRIMARY KEY,
  title varchar(60) NOT NULL,
  credit smallint DEFAULT 3
);

CREATE TABLE enrolls (
  student_id int REFERENCES students(id) ON DELETE CASCADE,
  course_id  int REFERENCES courses(id),
  score      numeric(5,2),
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO students (name, gender, age, city) VALUES
('张三','M',20,'北京'), ('李四','F',19,'上海'),
('王五','M',21,'北京'), ('赵六','F',22,'广州'),
('钱七','M',20,'上海');

INSERT INTO courses (title, credit) VALUES
('数据库',4), ('算法',3), ('操作系统',4), ('网络',3);

INSERT INTO enrolls VALUES
(1,1,88.5),(1,2,92.0),(2,1,76.0),(2,3,81.5),
(3,2,95.0),(3,4,68.0),(4,1,84.0),(5,3,90.5);

基础练习

练习 1：建表与插入

在 school 库中新建一张 teachers 表（id 自增主键、name 非空、department 字符串、hire_year 整数），并插入 3 条记录。

显示答案

CREATE TABLE teachers (
  id        serial PRIMARY KEY,
  name      varchar(40) NOT NULL,
  department varchar(40),
  hire_year  integer
);
INSERT INTO teachers (name, department, hire_year) VALUES
('陈老师','计算机',2015), ('林老师','数学',2018), ('吴老师','计算机',2020);

练习 2：更新与删除

把“李四”的年龄改为 20，并删除城市为“广州”的学生记录。

显示答案

UPDATE students SET age = 20 WHERE name = '李四';
DELETE FROM students WHERE city = '广州';

查询练习

练习 3：条件与排序

查询年龄 ≥ 20 且来自“北京”或“上海”的学生，按年龄降序排列。

显示答案

SELECT * FROM students
WHERE age >= 20 AND city IN ('北京','上海')
ORDER BY age DESC;

练习 4：聚合与分组

统计每个城市的学生数量，只显示数量大于 1 的城市。

显示答案

SELECT city, count(*) AS cnt
FROM students
GROUP BY city
HAVING count(*) > 1;

练习 5：多表连接

查询每位学生的姓名及其所选课程名称、成绩（三表 JOIN）。

显示答案

SELECT s.name, c.title, e.score
FROM students s
JOIN enrolls e ON e.student_id = s.id
JOIN courses c  ON c.id = e.course_id
ORDER BY s.name, c.title;

练习 6：子查询

查询成绩高于全部课程平均分的选课记录。

显示答案

SELECT * FROM enrolls
WHERE score > (SELECT avg(score) FROM enrolls);

进阶练习

练习 7：索引与执行计划

为 students(name) 建索引，并用 EXPLAIN 对比有无索引时按姓名查询的执行计划差异。

显示答案

CREATE INDEX idx_stu_name ON students(name);
EXPLAIN SELECT * FROM students WHERE name = '张三';
-- 有索引时应出现 Index Scan；无索引为 Seq Scan（全表扫描）

练习 8：视图

创建一个视图 v_good_students，仅包含平均分 ≥ 85 的学生姓名与平均分。

显示答案

CREATE VIEW v_good_students AS
SELECT s.name, avg(e.score) AS avg_score
FROM students s
JOIN enrolls e ON e.student_id = s.id
GROUP BY s.name
HAVING avg(e.score) >= 85;

练习 9：事务

用事务实现“王五选修操作系统”：先确认课程存在，再插入选课记录；若插入失败则回滚。

显示答案

BEGIN;
  INSERT INTO enrolls (student_id, course_id, score)
  VALUES (3, (SELECT id FROM courses WHERE title='操作系统'), NULL);
COMMIT;
-- 若报错执行 ROLLBACK;

精通学习资料

事务与隔离级别

PostgreSQL 提供四种事务隔离级别，默认是 Read Committed：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	不会*	可能	可能
Read Committed（默认）	否	可能	可能
Repeatable Read	否	否	可能*
Serializable	否	否	否

注：PostgreSQL 中 Read Uncommitted 实际等同于 Read Committed；Repeatable Read 下幻读通过 SSI 机制基本可避免。设置方式：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;  -- 同一事务内多次读一致
COMMIT;

执行计划 EXPLAIN

用 EXPLAIN (ANALYZE, BUFFERS) 查看真实执行代价、行数与缓存命中，是性能调优的核心工具。

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
-- Seq Scan = 全表扫描（慢）；Index Scan = 走索引（好）
-- 成本 (cost=...) 越低越好；actual time 是真实耗时

提示：读懂执行计划顺序：从最内层（叶子）往上看，关注是否有 Seq Scan 出现在大表、是否预估行数（rows）与实际（actual rows）偏差过大。

索引深入

-- 部分索引：只为热数据建索引，体积小
CREATE INDEX idx_active ON orders(user_id) WHERE status = 'pending';

-- 表达式索引：对函数结果建索引
CREATE INDEX idx_lower_email ON users(lower(email));

-- 覆盖索引（INCLUDE）：索引里直接带额外列，避免回表
CREATE INDEX idx_cover ON orders(user_id) INCLUDE (amount);

-- GIN：适合数组 / jsonb / 全文检索
CREATE INDEX idx_tags ON articles USING gin(tags);

B-tree（默认）：等值、范围、排序，最常用。
Hash：仅等值，PostgreSQL 10+ 支持 WAL 后可用。
GIN：多值/全文检索（数组、jsonb、tsvector）。
GiST：几何、范围、相似度检索（如 PostGIS）。
BRIN：超大数据量、按物理顺序的列（如时间序日志），体积极小。

分区表 (Partitioning)

把大表按范围 / 列表 / 哈希拆成多个子表，提升查询与维护效率。

-- 按时间范围分区
CREATE TABLE logs (
  id bigint, created_at timestamp, msg text
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2026q1 PARTITION OF logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

物化视图 (Materialized View)

把查询结果物理存储下来，适合昂贵聚合的重复查询；需手动 REFRESH 或用扩展定时刷新。

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) d, sum(amount) s
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW mv_daily_sales;
-- 可建唯一索引后使用 CONCURRENTLY 并发刷新，不阻塞读

窗口函数 (Window Functions)

在“不聚合行”的前提下做分组计算，是报表分析的利器。

SELECT name, dept, salary,
  rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
  sum(salary) OVER (PARTITION BY dept) AS dept_total,
  lag(salary) OVER (ORDER BY salary) AS prev_salary
FROM employees;

row_number() / rank() / dense_rank()：排名
lag() / lead()：取前/后行
sum()/avg() OVER：累计/滑动聚合

JSONB 与半结构化数据

CREATE TABLE profiles (
  id serial PRIMARY KEY,
  data jsonb
);
INSERT INTO profiles (data) VALUES ('{"name":"Tom","tags":["a","b"],"age":30}');

-- 取字段、判断包含、按路径索引
SELECT data->>'name', data->'tags' FROM profiles
WHERE data @> '{"age":30}';
CREATE INDEX idx_profile ON profiles USING gin(data);

PL/pgSQL 与触发器

CREATE OR REPLACE FUNCTION touch_updated()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_upd BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION touch_updated();

PL/pgSQL 支持变量、循环、异常处理，可写存储过程、函数、触发器，把复杂逻辑下沉到数据库。

复制与高可用

流复制 (Streaming Replication)：基于 WAL 的物理复制，一主多从，从库可读，用于读写分离与故障切换。
逻辑复制 (Logical Replication)：按表/行级发布订阅，可做跨版本迁移、部分数据同步。
高可用方案：Patroni + etcd、repmgr、PgPool-II、Keepalived。

-- 发布端
CREATE PUBLICATION mypub FOR TABLE users;
-- 订阅端
CREATE SUBSCRIPTION mysub CONNECTION 'host=master dbname=app'
  PUBLICATION mypub;

备份与恢复

# 逻辑备份单库
pg_dump -U user -h localhost mydb > mydb.sql
# 恢复
psql -U user -d mydb -f mydb.sql

# 物理基础备份 + WAL 归档（支持 PITR 时间点恢复）
pg_basebackup -D /backup/data -Ft -Xs -P

# 导出为自定义格式（更小、可选择性恢复）
pg_dump -Fc mydb > mydb.dump

警告：生产务必开启 WAL 归档 并定期演练恢复，备份没有验证过等于没备份。

性能调优要点

配置：shared_buffers（通常 25% 内存）、work_mem、effective_cache_size、max_connections 按需调整。
统计信息：定期 ANALYZE / VACUUM（或 Autovacuum 守护），保证规划器有准确统计。
慢查询：用 pg_stat_statements 扩展找出最耗时的 SQL。
连接池：用 PgBouncer 减少连接开销。

-- 开启语句统计扩展
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

常用扩展插件

扩展	用途
postgis	地理空间数据
pgvector	向量检索（AI Embedding）
pg_stat_statements	SQL 统计与慢查询
pg_trgm	模糊搜索 / 相似度
hstore	键值对（历史方案）
citext	不区分大小写文本

CREATE EXTENSION IF NOT EXISTS pgvector;

扩展学习资料

生态工具

psql：官方命令行，最强大，元命令丰富。
pgAdmin 4：官方图形界面，Web 化。
DBeaver / DataGrip：跨数据库 GUI，体验好。
pgcli：带自动补全的命令行客户端。
Flyway / Liquibase：数据库版本迁移管理（Schema as Code）。
PgBouncer：轻量连接池。

ORM 与框架集成

语言/框架	推荐
Python	SQLAlchemy、Django ORM、asyncpg
Java	Hibernate / JPA、jOOQ
Node.js	Prisma、TypeORM、pg（原生驱动）
Go	GORM、sqlx、pgx
Rust	SQLx、Diesel
PHP	Laravel Eloquent、Doctrine