第 12 章:从 SQLite 到 PostgreSQL
学了 11 章的 SQLite,最后要面对一个现实问题:真上生产,多数团队用的是 PostgreSQL。好消息是 SQL 语法八成通用;坏消息是剩下两成会让你的查询在迁移后突然出错。
学习目标
- 判断一个项目什么时候该从 SQLite 换到 PostgreSQL
- 会做类型映射,知道自增、布尔、日期在两边怎么写
- 说清标识符大小写和字符串比较的差异,避免"迁移后查不到数据"
- 走一遍迁移、备份与验证的完整流程
12.1 什么时候该换
SQLite 不是"玩具",很多桌面软件、边缘设备、单机服务用它跑得很好。但它有几个硬限制:
| 限制 | 表现 | 什么时候会成为问题 |
|---|---|---|
| 单写者 | 同一时刻只有一个连接能写 | 多用户同时下单、并发写日志 |
| 无网络协议 | 只能本地文件访问 | 多台服务器共用一个库 |
| 无用户/权限体系 | 文件权限就是全部 | 需要按角色控制访问 |
| 扩展能力弱 | 复杂分析、并行查询有限 | 报表、大数据量聚合 |
出现下面任一情况,就该考虑 PostgreSQL:
- 部署成多实例(负载均衡后面两台以上服务);
- 写入 QPS 明显上升,开始频繁
database is locked; - 需要
FOR UPDATE、更强的隔离级别、窗口函数做分析; - 需要 JSONB 查询、全文检索、
pgvector向量检索; - 需要标准的备份、监控、权限体系。
反过来说,如果你的项目是"单机工具、读多写少、数据量在 GB 级以内",SQLite 完全够用,换库只会增加运维成本。
12.2 类型映射:一张对照表
| 用途 | SQLite | PostgreSQL | 说明 |
|---|---|---|---|
| 主键/整数 | INTEGER | integer / bigint | 大表用 bigint |
| 金额 | INTEGER(存分) | numeric(12,2) 或 integer(存分) | 别用浮点 |
| 浮点 | REAL | double precision / real | 计算用,别存钱 |
| 字符串 | TEXT | text / varchar(n) | PG 的 text 无长度限制,推荐 |
| 布尔 | INTEGER 0/1 | boolean | PG 有原生布尔 |
| 日期 | TEXT YYYY-MM-DD | date | 迁移时要转换 |
| 时间戳 | TEXT | timestamptz | 带时区,统一存 UTC |
| JSON | TEXT | jsonb | PG 可直接查询字段 |
| 二进制 | BLOB | bytea | 很少用 |
几个要点:
- 金额:SQLite 里建议整数存分;PostgreSQL 里
numeric(12,2)是精确十进制,也可靠。REAL/double在任何库都别用来存钱。 - 时间:SQLite 的
TEXT排序正确,但没有时区概念;PostgreSQL 的timestamptz会做时区转换。迁移时把所有时间统一成 UTC,再导入。 - 布尔:
WHERE is_active = 1在 PG 里要改成WHERE is_active = true,或者直接WHERE is_active。
12.3 自增:从 INTEGER PRIMARY KEY 到 IDENTITY
SQLite:
id INTEGER PRIMARY KEYPostgreSQL 三种写法,按推荐度排序:
-- ① 推荐(PG 10+):标准 SQL 的 identity
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-- ② 常见旧写法:serial(本质是"自动建序列 + 默认值")
id serial PRIMARY KEY
-- ③ 手动管理序列(少见)
id integer PRIMARY KEY DEFAULT nextval('books_id_seq')IDENTITY 比 serial 更标准,权限和序列归属也更清晰。新项目用它。
拿刚插入的 id
SQLite 用 last_insert_rowid():
INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2025-11-17', 'pending');
SELECT last_insert_rowid();PostgreSQL 用 RETURNING,一条语句直接返回:
INSERT INTO orders (customer_id, ordered_at, status)
VALUES (1, '2025-11-17', 'pending')
RETURNING id;RETURNING 还能返回其他列,做"插入并立刻拿到结果"非常方便,SQLite 3.35 之后也支持了。
迁移后序列要对齐
用工具导入数据时,自增序列不会自动跳过已导入的 id。导入 100 行后,序列还停在 1,下一次插入就主键冲突。
修复(identity 列):
ALTER TABLE books ALTER COLUMN id RESTART WITH 101;serial 列则是:
SELECT setval('books_id_seq', (SELECT MAX(id) FROM books));这一步是迁移最容易漏的,漏了会在"第一次新增数据"时才爆出来。
12.4 大小写:迁移后最容易踩的坑
标识符:不加引号会被折叠成小写
PostgreSQL 里:
CREATE TABLE OrderItems (...); -- 实际建成了 orderitems
CREATE TABLE "OrderItems" (...); -- 保留大小写,但以后每次查询都要加引号规则是:不加双引号的标识符一律转小写;加了引号才区分大小写,但从此每次引用都必须带引号。
所以最佳实践是全部用小写 + 下划线(order_items),永远不加引号。SQLite 对大小写不敏感(OrderItems 和 orderitems 是同一张表),这个问题在 SQLite 上根本不会暴露,一到 PG 就集中爆发。
字符串比较:PG 区分大小写
-- SQLite:'SQL' LIKE 'sql' → 1(不区分)
-- PostgreSQL:'SQL' LIKE 'sql' → false(区分)PG 要忽略大小写,用 ILIKE:
SELECT title FROM books WHERE title ILIKE '%sql%';或者建索引时指定 lower():
CREATE INDEX idx_books_title_lower ON books (lower(title));
-- 查询时也要写 lower(title) LIKE lower('%sql%')排序:NULL 的位置相反
-- SQLite:NULL 最小,ASC 时排最前
-- PostgreSQL:ASC 时 NULL 默认排最后(NULLS LAST)第 4 章那个 ORDER BY author IS NULL, author ASC 的技巧在 PG 上可以直接写成:
ORDER BY author ASC NULLS LAST;跨库兼容的查询,显式写出 NULLS FIRST/LAST,别依赖默认值。
12.5 其他行为差异清单
| 行为 | SQLite | PostgreSQL |
|---|---|---|
GROUP BY 严格性 | 宽容,SELECT 非聚合列随便取 | 严格,必须出现在 GROUP BY 或聚合里 |
| 类型检查 | 动态类型,宽松 | 严格,WHERE price = '49' 报错 |
| 字符串拼接 | ` | |
| 分页 | LIMIT/OFFSET | LIMIT/OFFSET、FETCH FIRST(相同/兼容) |
| UPSERT | ON CONFLICT(3.24+) | ON CONFLICT(相同) |
| 外键 | 默认关闭,需 PRAGMA | 默认启用 |
| 事务隔离默认 | 串行化(单写者) | 读已提交 |
| 大小写折叠 | 不折叠 | 不加引号转小写 |
| 自增函数 | last_insert_rowid() | RETURNING |
第 1 行和第 2 行是最容易在迁移后报错的:SQLite 帮你兜住的那些"不规范写法",PG 会一条条指出来。这是好事——报错比默默算错强。
12.6 迁移流程:五步走完
假设要把小书店从 SQLite 搬到 PostgreSQL。
第一步:建目标库与表
把 init.sql 按 PG 语法改写(类型、自增、布尔)。不要直接拿 SQLite 的 init.sql 执行,INTEGER PRIMARY KEY 在 PG 里含义不同(它不会自增)。
第二步:导出数据
SQLite 可以导出成 SQL 或 CSV:
sqlite3 bookstore.db ".mode csv" ".headers on" ".output books.csv" "SELECT * FROM books;"或者用 Python 读出来写成 CSV/JSON,顺便做类型转换(日期字符串转 date、0/1 转布尔)。
第三步:导入
psql -d bookstore -c "\copy books FROM 'books.csv' CSV HEADER"\copy 是客户端命令,比服务端 COPY 更适合本地操作。数据量大时优先 COPY,比逐条 INSERT 快几个数量级。
第四步:对齐序列
ALTER TABLE books ALTER COLUMN id RESTART WITH (SELECT MAX(id) + 1 FROM books);每张有自增列的表都要做一遍。
第五步:验证
不要只看"导进去了多少行",要跑关键查询对比结果:
-- 行数
SELECT COUNT(*) FROM books; -- 应为 8
-- 聚合结果
SELECT ROUND(SUM(quantity * unit_price), 2) FROM order_items; -- 应为 1695.0
-- 边界数据
SELECT title FROM books WHERE author IS NULL; -- 应有《无名诗集》第 2 项和第 3 项是"语义验证":只对行数会发现不了类型转换错误,只有跑聚合和空值查询才能暴露。
12.7 备份与日常运维
备份
SQLite 用 .backup(不要直接 cp 正在写的库文件):
sqlite3 bookstore.db ".backup backup.db"PostgreSQL 用 pg_dump:
# 自定义格式,支持压缩和选择性恢复
pg_dump -Fc -d bookstore -f bookstore.dump
# 恢复
pg_restore -d bookstore_new bookstore.dump-Fc 比纯 SQL 文本更好:压缩、可并行恢复、能只恢复某张表。
三条日常习惯
- 定期验证备份能恢复。没验证过的备份等于没有备份;
- 开自动清理。PG 的
autovacuum默认开启,别关;SQLite 定期VACUUM回收空间; - 监控慢查询。PG 的
pg_stat_statements能按总耗时排序 SQL,比人肉猜准得多。
12.8 如果下一步是向量检索
PostgreSQL 有一个 SQLite 没有的优势:扩展。装 pgvector 之后,同一张表可以既有普通字段,又有向量列:
CREATE EXTENSION vector;
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
content text,
embedding vector(1536)
);
-- 按语义相似度排序
SELECT content FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'
LIMIT 5;这就是 RAG 的存储层雏形。细节留给 L4 的 RAG 课程,这里只需要知道:关系型 + 向量扩展可以共用一个库,不必一开始就引入独立的向量数据库。
12.9 回头看这门课
十二章节走完,回头看整条线其实只有四步:
| 阶段 | 你学会的事 | 对应章节 |
|---|---|---|
| 理解 | 数据库为什么存在、表怎么拆、主键是什么 | 1 |
| 会查 | SELECT、过滤、聚合、JOIN、子查询 | 2–7 |
| 会设计 | 约束、范式、索引、事务 | 8–10 |
| 会落地 | 安全访问、迁移到生产库 | 11–12 |
如果只记一句话,记这句:
能用约束表达的规则就写进库,能一次查完的就别循环查,写之前先想清楚 NULL 和索引。
SQL 不难,难的是它不会立刻告诉你"这样写会慢""这样写会错"。它总是在数据量涨上来之后才收账。这门课能做的,是让你在写的时候就知道账单在哪。
本章小结
| 要点 | 说明 |
|---|---|
| 换库的信号 | 多实例、并发写、复杂分析、需要扩展 |
| 类型映射 | 整数/金额/时间/布尔逐项对照,别用浮点存钱 |
自增用 IDENTITY | 取 id 用 RETURNING |
| 导入后必须对齐序列 | 否则第一次插入就主键冲突 |
| 标识符全小写 | PG 不加引号会折叠成小写 |
| 字符串比较区分大小写 | 忽略大小写用 ILIKE |
GROUP BY 更严格 | SQLite 的宽容到 PG 会报错 |
NULL 排序位置相反 | 跨库查询显式写 NULLS FIRST/LAST |
| 迁移要语义验证 | 行数对了不代表数据对 |
| 备份要验证能恢复 | 没验证过的备份等于没有 |
课程到此结束
下一步可以去 L3 的《Redis 与向量数据库》——那里会讲缓存、键值模型和向量检索;也可以直接进 L4 的 RAG,把这里的 pgvector 用起来。
SQL 这门手艺,剩下的靠练:拿一个真实的小项目,把表设计一遍,把慢查询跑一遍 EXPLAIN,比读十篇文章都管用。
👉 返回 课程主页