PostgreSQL增删改查实战：从建表到事务的避坑指南

刚接触 PostgreSQL 的同学，尤其是从 MySQL 转过来的那批，上手第一个星期基本都在跟报错较劲。PostgreSQL 语法跟 MySQL 看着差不多，但骨子里很多习惯是反着的——单引号、双引号、布尔值、自增列、NULL 判断，样样都有讲究。这篇我不打算堆概念，直接按我平时写业务 SQL 的习惯，把增删改查（CRUD）从头到尾捋一遍，顺带把那些“文档里不写、但跑一遍就翻车”的细节全标出来。里面涉及的建表、INSERT 回显、联表 UPDATE、DELETE 恢复、事务提交这些东西，都是实际干活天天要碰的。适合刚入门的同学照着敲，也适合准备从 MySQL 迁过来的朋友做个对照参考。

1. 动手之前：连库，以及先看清你手里的 PostgreSQL

1.1 确认环境和连接方式

很多人装完 PostgreSQL，第一步就卡在“怎么连上”。它默认端口是 5432，如果之前机器上装过 MySQL（默认 3306），别想当然改端口，连接时明确指定即可。

命令行连接是最直接的方式：

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

如果不写-d，默认会和用户名同名的数据库，postgres用户默认连的是postgres数据库，这个库相当于 MySQL 里的系统库，不要在里面建业务表。我见过有人把表建在 postgres 库里，后面备份、权限全乱套，这属于基本功没打牢。启动后建议先跑一句：

select version();

看清你的版本。PG 10 之前和之后的写法差异不小，比如IDENTITY列就是 10 版本才有的新特性，网上很多老教程还在用serial，照着抄不一定报错，但不够规范。

1.2 练习库的准备

我习惯建一个独立的练习库，名字随意，比如demo：

create database demo;

创建完用\c demo切换。注意 PostgreSQL 的库名、表名、列名在不加双引号时会被折叠成小写。也就是说，你写CREATE DATABASE Demo，实际创建的是demo。如果你用双引号写成CREATE DATABASE "Demo"，那它就会记住大写 D，以后每次连接都得加双引号，纯给自己找罪受，规范做法就是全部小写加下划线。

2. 建表才是增删改查的地基：字段类型与常用约束

2.1 类型选择的门道

从 MySQL 过来的人，第一坑就在自增列。MySQL 写惯了AUTO_INCREMENT，PG 里根本认不了。PostgreSQL 有两种做法：

-- 老写法：serial create table users ( id serial primary key, name text ); -- 新写法：generated identity（PG 10+） create table users ( id integer generated always as identity primary key, name text );

serial本质是创建序列后给列加默认值，靠的是序列的nextval()。generated always as identity更干净，它禁止你手动往 id 里插值，能防住“误插一个指定 id 打乱后续主键节奏”的问题。我现在新表一律用 identity，老表才继续用 serial。

字段类型上，PostgreSQL 的text和varchar(n)在性能上没有差异（不像某些数据库那样 text 有限制），所以短字符串用varchar纯粹是为了约束长度，长文本直接text。金额字段用numeric(12,2)，不要用float——浮点算钱，月底对账差几分钱这种事，谁用谁知道。时间字段，凡是涉及业务时间的，一律timestamptz（带时区），它会自动换算成 UTC 存储，读出来再按会话时区还原，比不带时区的timestamp稳得多。

2.2 约束和默认值

建表时约束建议一次写全，不然后面补约束代价更大。常用约束有：

• 主键primary key
• 非空not null
• 唯一unique（注意 NULL 不算重复，多个 NULL 是合法的，这个后面细讲）
• 检查check，比如金额check (price >= 0)
• 默认值default

默认值这里有个坑：default只对“这条 insert 没带这个字段”的情况生效，如果你显式插入null，默认值不会填进去，而是真的存成 NULL。想强制不让 NULL 进来，就得靠not null约束。

2.3 删除、重建表的顺序问题

业务上一个常见的优化需求是“清空这张表重新导数据”。如果你直接drop table再create table，依赖它的视图、外键和权限全部会丢。我建议保留表结构、只清数据用truncate，后面会单独说。要是确实要重建，记得先确认没有其他表外键引用它，否则 drop 会直接报错“cannot drop table ... because other objects depend on it”。

3. INSERT：插入数据的正确姿势

3.1 基础 INSERT 与批量插入

单行插入很简单：

insert into users (name, email) values ('张三', 'zhangsan@example.com');

PostgreSQL 对大小写敏感的是标识符，字符串值用单引号，这是死规矩。你写成双引号包字符串，它立刻报错“column ... does not exist”，因为双引号被当成标识符了。MySQL 的INSERT IGNORE、ON DUPLICATE KEY UPDATE风格在 PG 里也不通用，PG 用的是标准 SQL 的ON CONFLICT。

批量插入用一条 INSERT 带多组值，性能比循环单插好一个量级：

insert into users (name, email) values ('李四', 'lisi@example.com'), ('王五', 'wangwu@example.com'), ('赵六', 'zhaoliu@example.com');

一次插几千行没问题，但如果数据有几万到几十万行，单条 SQL 太长，可以考虑copy或pg的格式化导入，这个属于数据导入范畴，不是基本 CRUD 的重点。

3.2 RETURNING：PostgreSQL 的回显利器

MySQL 插入后要拿到自增 id，通常得再查一遍last_insert_id。PG 一条 SQL 直接解决：

insert into users (name, email) values ('张三', 'zhangsan@example.com') returning id;

returning后面可以跟列、表达式，甚至returning *（返回整行）。这个特性不止 INSERT 能用，UPDATE、DELETE 也支持。实际开发中，我经常用它拿回后台生成的默认值，比如created_at、id，省掉后续一次查询，非常顺手。

3.3 ON CONFLICT 做 upsert

业务里“有就更新，没有就插入”的场景非常常见，PG 里这么写：

insert into users (id, name, email) values (1, '张三', 'zhangsan@example.com') on conflict (id) do update set name = excluded.name, email = excluded.email;

excluded是指“当前这次插入尝试写入的那些值”。它的意思是：如果 id 冲突，就用本次插入的值去更新对应字段。不需要更新所有字段的话，只列你要更新的列即可。如果唯一键是邮箱，冲突目标就写(email)，一样成立。这个特性比“先 select 再 update/insert 二选一”节省一次往返，数据库压力也小很多。

3.4 从查询结果直接灌入

有时候要把一张表的数据搬到另一张表：

insert into users_bak (name, email) select name, email from users where created_at < '2024-01-01';

这是标准的「INSERT ... SELECT」，列数、类型务必对齐，而且它不会触发on conflict，如果需要去重，得先在 select 里做distinct。

4. SELECT：查询不止是“select *”

4.1 WHERE、排序、LIMIT

基础查询不啰嗦，重点讲容易翻车的地方：

select id, name, email from users where status = 'active' order by created_at desc limit 20;

limit和offset是 PG 常用分页手段，但大偏移量会越查越慢，因为数据库要逐个跳过前面的行。数据量大了，正确姿势是“键集分页”：

select id, name from users where id > 1000 order by id limit 20;

靠上一页最大的 id 往后翻，而不是offset 9999 limit 20，这个自己压测一下就能感受到差距。另外，order by里如果字段是 NULL，PG 默认 NULL 排最前面（ASC），MySQL 是排最后，两个库行为不一样，如果对排序要求严格，建议写NULLS LAST或NULLS FIRST显式控制。

4.2 NULL 的处理

NULL 是基本 SQL 里最阴险的东西。很多人会写：

select * from users where email = null;

结果一条都查不出来。NULL 不等于任何值，包括 NULL 自己。判断空值只能用is null或is not null。更坑的是not in遇到 NULL，比如：

select * from users where id not in (select user_id from orders);

只要orders.user_id里出现一个 NULL，整个查询返回空——因为id = NULL既不是真也不是假，是未知，not in的语义全被破坏了。我踩过这个坑之后，凡是not in一律改成not exists：

select * from users u where not exists ( select 1 from orders o where o.user_id = u.id );

这也是我强烈建议新人的一条铁律：业务代码里不要写not in，全用not exists替代。另外一个实用点：nullif函数可以在比较时把空串当 NULL：

select * from users where nullif(email, '') is null;

这条能把“空字符串”和“NULL”一并查出来，做数据清洗时很常用。

4.3 去重与去重后取一行

普通去重用distinct。但我见的更多场景是“按某个字段分组，每组取最新一条”，这种用distinct on最干净：

select distinct on (user_id) id, user_id, order_amount from orders order by user_id, created_at desc;

它的意思是：对user_id去重，每个 user_id 保留在order by里排最前的那一条。所以order by的第一列必须和distinct on的列一致，否则报错。这个写法比窗口函数更直观，适合临时查数。如果后续还要关联别的表，注意distinct on只对输出的行去重，你要是 select 多列关联数据，逻辑不变，只是输出变宽。

4.4 字符串与日期处理的几个高频函数

写业务 SQL 天天要碰字符串和日期。PG 字符串拼接用||：

select first_name || ' ' || last_name as full_name from users;

MySQL 的concat在 PG 也能用，但||更接近标准，连字段和字符串混拼时也更省事。字符串聚合，PG 里有string_agg：

select dept_id, string_agg(name, ',') from users group by dept_id;

用法相当于 MySQL 的group_concat。日期这块，PG 里最简单实用的就是区间过滤和截断：

-- 近 7 天 select * from orders where created_at >= now() - interval '7 days'; -- 按天分组统计 select date_trunc('day', created_at) as day, count(*) from orders group by day order by day desc;

now()返回当前事务开始时间，同一事务内不会变，这个点设计得很精准。跨时区项目，用timestamptz存时间，展示时at time zone 'Asia/Shanghai'转成本地时间，比你在应用层手写换算稳得多。

5. UPDATE：从单行到联表更新

5.1 基础 UPDATE 和 RETURNING

UPDATE 基础语法：

update users set status = 'inactive', updated_at = now() where id = 1 returning id, status, updated_at;

where条件务必写清楚，不然就是全表更新，生产环境一次误操作能把整张表的字段全洗掉。我用 UPDATE 之前一定先跑一遍同条件的 SELECT，确认影响范围。就算有把握，也建议边写边想：这个条件的索引在不在？where status = 'inactive'如果 status 没有索引，表一大就是全表扫描，几千行无所谓，百万行就得等。

5.2 联表 UPDATE

需要“根据 A 表的数据更新 B 表”，这是 MySQL 和 PG 差得比较大的地方。MySQL 可以UPDATE a JOIN b ON ... SET ...，PG 不能用 JOIN 直接拼在 UPDATE 后面，得用FROM子句：

update users u set email = o.contact_email from orders o where o.user_id = u.id and o.contact_email is not null;

注意这里的where既承担“关联条件”也承担“过滤条件”。多条订单对应一个用户时，PG 会随机选一行来更新（不保证是哪条订单的邮箱），所以关联前要确认数据唯一性。如果不唯一，建议先做子查询去重：

update users u set email = t.email from ( select distinct on (user_id) user_id, contact_email as email from orders order by user_id, created_at desc ) t where t.user_id = u.id;

5.3 批量更新时的锁与性能注意

批量 UPDATE 大表时，PG 的行锁、锁等待是实打实的问题。如果你用一个 UPDATE 更新 100 万行，事务会一直持有大量行锁，其他会话的读写会被堵住。凌晨跑任务没问题，白天在线系统这么干，必然引来线上告警。

我的做法是分批更新，比如每次 5000 行：

update users set status = 'inactive' where status = 'active' and id in ( select id from users where status = 'active' order by id limit 5000 );

跑完一批再跑下一批，循环到影响行数为 0。每批提交一次，锁释放得快，也能随时暂停观察负载。配合pg_stat_activity看看有没有长事务，就能做到心里有数。

6. DELETE：删除、清空与误删抢救

6.1 DELETE 与 TRUNCATE

delete from users where id = 1这是逐行删除，会触发行级触发器，会逐条写 WAL 日志，数据量一大就慢。要清空整张表，用truncate：

truncate table users;

truncate直接释放表的存储，速度快到离谱，但它会重置表的自增序列吗？默认不重置。如果你想清空数据并且让自增 id 从头开始，得加restart identity：

truncate table users restart identity;

这是最推荐的做法。truncate还有一层：它不能对有外键引用它的表直接用，除非在语句里也把关联表一起列出，或者加cascade：

truncate table users cascade;

cascade会把依赖该表的外键表也一并清空（除非外键是on delete cascade）。所以这个命令要谨慎，你得清楚哪些表会中招。我的习惯：先查一下外键关系再动手，绝不盲敲 cascade。

6.2 CASCADE 与约束

外键on delete cascade的意思是“删主表记录时，自动删除子表关联记录”。设计上它不是坏东西，但在生产环境要权衡。比如orders外键到users，你delete from users where id = 1，所有相关订单瞬间被删，数据恢复难度直线上升。我的经验：核心业务表，外键尽量用on delete restrict（默认行为），删不掉就报错让你知道有子数据存在；要清理，先显式处理子表，再删主表。

6.3 误删数据的抢救思路

写“基本 SQL”的文章本不该牵涉恢复，但删错数据是三年来我见过最多的事故。讲点保命的东西：PG 的 MVCC 机制决定了，旧版本的数据在vacuum之前还留在数据文件里。如果你刚执行完 DELETE，立刻停止一切写操作，有一定概率从 WAL 日志或堆文件里抢救数据。有几种思路：

1. 如果那个时刻点有备份：直接做基于时间点恢复（PITR）到误删之前，这是最稳的。
2. 没有备份但有 WAL 归档：同样靠 PITR 或pg_waldump定位误删事务，再重建一个临时实例拉取数据。
3. 什么都没配置：难度很大，基本只能自认倒霉。

所以对“DELETE 时效性数据”这件事，我常年坚持两条习惯：一是删除前先select count(*)和select * limit 10看清楚删什么；二是所有核心生产库必须打开连续归档并定期做全量备份测试恢复。

7. 事务：增删改查的“后悔药”

7.1 BEGIN / COMMIT / ROLLBACK

基本 CRUD 单条语句执行时，PG 默认是隐式事务，每句自动提交。但如果你要“先插订单、再扣库存、再更新用户余额”，就得手动开一个事务，保证要么全成功要么全失败：

begin; insert into orders (user_id, amount) values (1, 100); update users set balance = balance - 100 where id = 1; update products set stock = stock - 1 where id = 10; commit;

中途任何一条语句报错，事务自动变成 aborted 状态，后面所有语句都不让执行，只能rollback。还有一点：PG 的 DDL（建表、改字段）也支持事务回滚，这点跟 MySQL 不一样，你可以在事务里create table，回滚后表消失，这非常利于做迁移脚本调试。

7.2 读已提交与未提交的变化

PG 默认隔离级别是read committed，意思是每个语句只能看到已提交的数据。当你在事务 A 里插入一条数据但还没 commit，事务 B 是看不到这条数据的；同样，事务 A 自己能看到自己没提交的修改。这个特性容易引发一个经典场景：你开了事务，插入数据，然后在同一个连接里做查询，数据在；但你在另一个窗口查，怎么都查不到——这不是 BUG，是隔离级别，很多人第一次遇到就慌。真要同时看到，可以在事务里改隔离级别为repeatable read，但这又带来序列化失败的可能，不建议新手碰。

7.3 SAVEPOINT

事务里某一步出问题，不想全盘回滚，只想回退到中间某个点，可以用保存点：

begin; insert into users (name) values ('张三'); savepoint sp1; insert into users (name) values ('李四'); -- 李四这条插入出错了 rollback to sp1; -- 张三还在，把它提交 commit;

这对批量导入特别有用：一批数据里偶发几行坏数据，用保存点把坏行隔离掉，其余正常入库。比起“整批失败再重头来”，效率高很多。

8. 常见报错与排查实录

8.1 高频报错速查表

这里面最烦的是“类型不匹配”。比如你从 CSV 里读出来的user_id是字符串，直接拼 SQL 拿去和 integer 列比较，PG 会拒绝隐式转换并报 operator 不存在，MySQL 通常默默做了转换。PG 的严格是特性，不是缺点，它能拦住大量隐性错误。

8.2 一个典型场景：外部工具连不上数据库

Navicat、pgAdmin、DBeaver 这类工具连不上 PG，十有八九不是密码错，而是这三件事：

1. 服务没启动。Windows 上服务名叫postgresql-x64-16，用管理员身份运行net start postgresql-x64-16。Linux/macOS 用pg_ctl或 systemd 查状态。
2. pg_hba.conf 不许远程连接。默认配置只允许本机127.0.0.1用 scram-sha-256 认证，要远程就连，得在配置文件里加一条允许网段的规则，然后pg_ctl reload。
3. 端口被防火墙挡了。5432 没放行，外网当然连不上。

我自己排查顺序是：先psql本机连一次，本机能连说明服务没毛病；再用工具连，失败就看错误是“timeout”还是“auth failed”。“timeout”多半是网络和防火墙，“auth failed”才是密码或认证方式问题，方向不一样，别对着密码改半天网络配置。

还有一个细节：PostgreSQL 客户端默认对密码用 SCRAM 加密校验，如果你的服务版本旧（10 以下）、密码里带特殊字符，部分老客户端工具连不上，升级一下工具版本基本能解。

再分享一个我长期在用的排查命令：

select pid, state, wait_event_type, query_start, now() - query_start as duration, query from pg_stat_activity where state = 'active' order by query_start;

看看有没有长期处于 active 状态的慢查询，有没有锁等待，都能在这里一行看出来。这个视图对定位“数据库怎么突然卡了”这类问题，价值比翻任何日志都高。

我个人在这条路上最深的体会是：SQL 这种东西，看十篇教程不如自己跑通一遍。你拿一个本地 PG 实例，建三张有关联的表，往里插几十行数据，然后反复练 INSERT 的 upsert、SELECT 的 distinct on、UPDATE 的联表、DELETE 的级联清理，每个都加上returning看一眼输出，跑过一次就忘不掉。等到你发现“哦，原来这个报错是这么回事”的时候，基本就算是真正上手了。