Skip to content
Lv1SQL 与数据库进度 12 / 12

第 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 类型映射:一张对照表 ​

用途SQLitePostgreSQL说明
主键/整数INTEGERinteger / bigint大表用 bigint
金额INTEGER(存分)numeric(12,2) 或 integer(存分)别用浮点
浮点REALdouble precision / real计算用,别存钱
字符串TEXTtext / varchar(n)PG 的 text 无长度限制,推荐
布尔INTEGER 0/1booleanPG 有原生布尔
日期TEXT YYYY-MM-DDdate迁移时要转换
时间戳TEXTtimestamptz带时区,统一存 UTC
JSONTEXTjsonbPG 可直接查询字段
二进制BLOBbytea很少用

几个要点:

  • 金额: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:

sql
id INTEGER PRIMARY KEY

PostgreSQL 三种写法,按推荐度排序:

sql
-- ① 推荐(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():

sql
INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2025-11-17', 'pending');
SELECT last_insert_rowid();

PostgreSQL 用 RETURNING,一条语句直接返回:

sql
INSERT INTO orders (customer_id, ordered_at, status)
VALUES (1, '2025-11-17', 'pending')
RETURNING id;

RETURNING 还能返回其他列,做"插入并立刻拿到结果"非常方便,SQLite 3.35 之后也支持了。

迁移后序列要对齐 ​

用工具导入数据时,自增序列不会自动跳过已导入的 id。导入 100 行后,序列还停在 1,下一次插入就主键冲突。

修复(identity 列):

sql
ALTER TABLE books ALTER COLUMN id RESTART WITH 101;

serial 列则是:

sql
SELECT setval('books_id_seq', (SELECT MAX(id) FROM books));

这一步是迁移最容易漏的,漏了会在"第一次新增数据"时才爆出来。


12.4 大小写:迁移后最容易踩的坑 ​

标识符:不加引号会被折叠成小写 ​

PostgreSQL 里:

sql
CREATE TABLE OrderItems (...);   -- 实际建成了 orderitems
CREATE TABLE "OrderItems" (...); -- 保留大小写,但以后每次查询都要加引号

规则是:不加双引号的标识符一律转小写;加了引号才区分大小写,但从此每次引用都必须带引号。

所以最佳实践是全部用小写 + 下划线(order_items),永远不加引号。SQLite 对大小写不敏感(OrderItems 和 orderitems 是同一张表),这个问题在 SQLite 上根本不会暴露,一到 PG 就集中爆发。

字符串比较:PG 区分大小写 ​

sql
-- SQLite:'SQL' LIKE 'sql' → 1(不区分)
-- PostgreSQL:'SQL' LIKE 'sql' → false(区分)

PG 要忽略大小写,用 ILIKE:

sql
SELECT title FROM books WHERE title ILIKE '%sql%';

或者建索引时指定 lower():

sql
CREATE INDEX idx_books_title_lower ON books (lower(title));
-- 查询时也要写 lower(title) LIKE lower('%sql%')

排序:NULL 的位置相反 ​

sql
-- SQLite:NULL 最小,ASC 时排最前
-- PostgreSQL:ASC 时 NULL 默认排最后(NULLS LAST)

第 4 章那个 ORDER BY author IS NULL, author ASC 的技巧在 PG 上可以直接写成:

sql
ORDER BY author ASC NULLS LAST;

跨库兼容的查询,显式写出 NULLS FIRST/LAST,别依赖默认值。


12.5 其他行为差异清单 ​

行为SQLitePostgreSQL
GROUP BY 严格性宽容,SELECT 非聚合列随便取严格,必须出现在 GROUP BY 或聚合里
类型检查动态类型,宽松严格,WHERE price = '49' 报错
字符串拼接`
分页LIMIT/OFFSETLIMIT/OFFSET、FETCH FIRST(相同/兼容)
UPSERTON 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:

bash
sqlite3 bookstore.db ".mode csv" ".headers on" ".output books.csv" "SELECT * FROM books;"

或者用 Python 读出来写成 CSV/JSON,顺便做类型转换(日期字符串转 date、0/1 转布尔)。

第三步:导入 ​

bash
psql -d bookstore -c "\copy books FROM 'books.csv' CSV HEADER"

\copy 是客户端命令,比服务端 COPY 更适合本地操作。数据量大时优先 COPY,比逐条 INSERT 快几个数量级。

第四步:对齐序列 ​

sql
ALTER TABLE books ALTER COLUMN id RESTART WITH (SELECT MAX(id) + 1 FROM books);

每张有自增列的表都要做一遍。

第五步:验证 ​

不要只看"导进去了多少行",要跑关键查询对比结果:

sql
-- 行数
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 正在写的库文件):

bash
sqlite3 bookstore.db ".backup backup.db"

PostgreSQL 用 pg_dump:

bash
# 自定义格式,支持压缩和选择性恢复
pg_dump -Fc -d bookstore -f bookstore.dump

# 恢复
pg_restore -d bookstore_new bookstore.dump

-Fc 比纯 SQL 文本更好:压缩、可并行恢复、能只恢复某张表。

三条日常习惯 ​

  1. 定期验证备份能恢复。没验证过的备份等于没有备份;
  2. 开自动清理。PG 的 autovacuum 默认开启,别关;SQLite 定期 VACUUM 回收空间;
  3. 监控慢查询。PG 的 pg_stat_statements 能按总耗时排序 SQL,比人肉猜准得多。

12.8 如果下一步是向量检索 ​

PostgreSQL 有一个 SQLite 没有的优势:扩展。装 pgvector 之后,同一张表可以既有普通字段,又有向量列:

sql
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,比读十篇文章都管用。

👉 返回 课程主页

本文阅读--次|全站访问--次|访客--人