★ FEATURED ARTICLE PostgreSQL里那几个INSERT INTO的细节我赌你未必全知道。INSERT INTO大概是我们在PostgreSQL里写得最多的SQL语句但恰恰是这种“看起来越简单”的东西坑越深。你在MySQL或者SQLite里用INSERT的直觉换到PostgreSQL的某些场景下会直接翻车主键冲突可能不是立刻报错而是卡住想取回自增ID要会用RETURNING批量Write的正确姿势也不是循环单条插入。这篇文章基于我日常维护几个生产PostgreSQL实例的实操经验把INSERT INTO从语法基础、冲突处理、批量性能、锁机制到常见坑一次性梳理透适合正在从其他数据库转到PostgreSQL的同学也适合那些被线上慢插入或死锁搞到头大的朋友。1. 语法全貌从最基础的写法开始1.1 最常见的四种基础写法PostgreSQL的INSERT语法比很多数据库都要宽松先列几种最常见的用法因为后面所有进阶玩法都建立在它们之上-- 写法一显式指定列名最推荐 INSERT INTO users (name, email, age) VALUES (张三, zhangsanexample.com, 28); -- 写法二省略列名能用但别常用 INSERT INTO users VALUES (1, 张三, zhangsanexample.com, 28); -- 写法三一条SQL插入多行 INSERT INTO users (name, email, age) VALUES (张三, zhangsanexample.com, 28), (李四, lisiexample.com, 32), (王五, wangwuexample.com, 25); -- 写法四从查询结果插入 INSERT INTO user_backup (name, email, age) SELECT name, email, age FROM users WHERE created_at 2024-01-01;写法三的性能优势后面专门讲这里先记住一个结论能用多行VALUES就不要写循环单条INSERT。写法二省略列名时VALUES里的值必须和表定义列的顺序完全一致一个都不能少。这种写法最大的问题是隐式依赖表结构。某天你在表中间加了一个字段所有不带列名的INSERT语句全部报错而且报错信息是“列数量不匹配”排查起来烦得很。生产环境我几乎不用这种写法。写法四乍一看很直白但有一个容易忽略的性能细节INSERT INTO ... SELECT 会全程持锁扫描源表。如果源表数据量很大建议加条件分批捞或者考虑先落到临时表再插入避免长时间锁住业务表。1.2 为什么我坚持显式写列名很多“老司机”在演示SQL时喜欢把列名省略看起来简洁。但实际维护数据库的人心里都有数显式列名本质上是一种防御性编程它把你的写入意图和表结构解耦了。至少有四个理由值得坚持表结构调整时显式列名不会因为字段顺序变化而静默出错别人review你的SQL时一眼就能看出你往哪些列写了什么不需要去翻表结构后续用脚本来生成INSERT时显式列名可以让生成逻辑更可控写数据迁移脚本时显式列名天然支持“只插入部分字段”的场景。如果你还在用ORM框架比如某主流Python ORM或者Java侧的Mapper默认生成的SQL通常也是显式列名的。这也从侧面说明省略列名只是一个语法糖生产环境里少用为好。1.3 DEFAULT关键字到底能干什么PostgreSQL对DEFAULT的处理非常灵活这是很多新手没注意到的点-- 某几个字段用默认值 INSERT INTO orders (order_no, user_id, amount, status) VALUES (ORD-20250101-001, 42, 99.80, DEFAULT); -- 全部字段都用默认值 INSERT INTO orders (order_no, user_id, amount, status) VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT);DEFAULT的作用是让PostgreSQL用列的默认值表达式来填充。常见的默认值是序列的nextval、当前时间戳、UUID生成函数等。真正实用的场景是当你只覆盖部分字段时比如一个订单表有created_at默认取当前时间、status默认是pending你INSERT时完全可以不写这两个字段DB自动帮你补好。这里有个细节值得记住如果你明确写DEFAULT关键字那么即使列允许NULL它也是用列的默认值而不是NULL。这和“不写该列”语义一致但显式写出来在某些代码生成器里会让意图更清晰。碰上限流/接口字段校验比较严格的团队这种写法能省不少沟通成本。2. 进阶功能RETURNING 和 ON CONFLICT 的正确玩法2.1 RETURNING不只是为了拿自增主键在我维护的某个订单系统里创建订单后立刻需要拿到订单号去调支付接口。早期代码里大家习惯先INSERT再SELECT max(id)或者根据某个业务字段反查这在高并发下纯属给自己挖坑——你查到的可能不是你自己刚插入的那行因为别的请求也插了同类的数据。PostgreSQL的RETURNING子句就是干这个的INSERT INTO orders (user_id, amount, status) VALUES (42, 99.80, paid) RETURNING id, order_no, created_at;这一条SQL执行完你不仅能拿到id还能拿到所有默认值生成的数据。更重要的一点是RETURNING是在语句层面统一处理的多行插入时它返回所有插入行而不是只返回最后一条INSERT INTO order_items (order_id, sku, qty) VALUES (1001, SKU-A, 2), (1001, SKU-B, 1), (1001, SKU-C, 5) RETURNING id, sku, qty;返回结果会是一个包含三行数据的查询结果集。后端代码里直接遍历这个结果集比逐条插入再逐条反查性能好太多网络往返次数直接从N次降到1次。RETURNING还支持表达式如果你想在返回前做点计算也完全没问题INSERT INTO price_history (product_id, price) VALUES (77, 199.00) RETURNING product_id, price * 1.1 AS price_after_tax;2.2 ON CONFLICT处理唯一键冲突的正确姿势用INSERT的时候最难受的错误就是“duplicate key value violates unique constraint”。以前很多人处理这种冲突的方法是先查一遍再决定插入还是更新既不优雅又存在并发窗口查完到插入之间的空档里别人可能已经插进去了。PostgreSQL的ON CONFLICT直接把这步放到数据库里原子完成。它有两种主要形式-- 冲突时啥也不做 INSERT INTO user_tags (user_id, tag) VALUES (42, postgresql) ON CONFLICT DO NOTHING; -- 冲突时更新指定字段 INSERT INTO counters (page_url, views) VALUES (/postgresql-insert/, 1) ON CONFLICT (page_url) DO UPDATE SET views counters.views 1;DO NOTHING的语义是如果插入的行和现有行在唯一约束上冲突就跳过不插入也不报错。适合做去重、打标签这类幂等写入。DO UPDATE的语义则是把“插入”变成“插入或更新”也就是所谓UPSERT。注意这里有一个关键字EXCLUDED它代表的是“本来试图插入但冲突了的那行数据”。比如INSERT INTO user_points (user_id, points) VALUES (42, 10) ON CONFLICT (user_id) DO UPDATE SET points user_points.points EXCLUDED.points;上面的SQL每次执行要么给新用户建一条记录要么给老用户在现有积分数上增加10。这是积分系统、统计系统里非常经典的需求。2.3 冲突目标写不对语句直接报错ON CONFLICT有一个容易踩的细节语法上分为“指定冲突目标”和“不指定冲突目标”两种。DO NOTHING可以不指定冲突目标因为它的语义是“任何唯一约束冲突都忽略”但DO UPDATE必须指定冲突目标也就是必须告诉数据库你担心的是哪个索引/约束冲突-- 可以这样把主键当作冲突目标 ON CONFLICT (id) DO UPDATE SET ... -- 也可以这样把某个唯一索引的表达式/名字当作目标 ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET ...如果你对没有唯一约束的列写ON CONFLICT (email)数据库会直接报错“there is no unique or exclusion constraint matching the ON CONFLICT specification”。这个错误在开发环境经常出现本质原因就是你列的那个字段其实没有建唯一索引。记住ON CONFLICT的冲突目标必须对应实际存在的唯一约束、主键约束或者排他约束不能凭空指定。3. 批量插入与性能优化从数据量级决定策略3.1 多行VALUES一条SQL搞定我见过不少项目后端代码写一个for循环一条一条INSERT一千条数据就要执行一千次网络往返。这里面的浪费不只是时间还有数据库端每条语句的解析、计划生成、事务提交开销。正确做法是用多行VALUESINSERT INTO products (name, price, stock) VALUES (商品A, 19.90, 100), (商品B, 29.90, 50), ... (商品Z, 9.90, 200);一次写多少行合适这不是一个固定值但根据我的实测经验500到1000行一批是性价比比较高的范围。超过这个量单条SQL的解析成本和内存占用会明显上升而且万一中间有某一行的数据导致整体回滚一批千行以上的数据重来代价太大。分批插入时每一批最好都包在事务里。这样这批数据要么全成功要么全失败不会留下一半数据然后你还要再写一套清理逻辑。像某次做历史数据迁移我就是每5000行一个事务批次之间适当停顿配合进度日志跑起来又稳又可控。3.2 事务批量插入 vs 自动提交的单条插入同样的1000条数据两种写法的差别非常大循环单条INSERT默认autocommit每一条都独立提交每条都要等磁盘日志落到WAL总耗时通常是批量的5到10倍包在一个事务里的多值INSERT提交只发生一次WAL日志刷盘一次性能自然好。当然事务也不是万能的。一个事务太大比如一次塞几百万行对关系型数据库来说会让单事务的undo/事务信息占用膨胀一旦失败回滚要很久。所以我的习惯是“批次大小适中事务包裹每批”而不是试图一个事务干完全部。3.3 数据量再大就直接上COPY当你要导入的数据量达到十万、百万级别时INSERT即使多行VALUES也不再是最优解。PostgreSQL自带的COPY命令才是大批量导入的王者-- 服务端COPY要求文件在数据库服务器本地 COPY products (name, price, stock) FROM /data/products.csv DELIMITER , CSV HEADER; -- 或者psql里的 \copy文件在客户端本地 \copy products (name, price, stock) FROM products.csv WITH (FORMAT csv, HEADER true);COPY比INSERT快得多原因在于它绕过了很多逐行处理的开销直接走底层批量写入通道同时还能配合事务保证整体原子性。我自己做过一个粗略对比导入50万行CSVINSERT分批跑大概要两三分钟COPY基本十几秒搞定差距能有一个数量级。所以合理的策略是数据量级推荐方案单条或少量INSERT RETURNING百到千条多值VALUES包事务分500~1000条一批万到百万条COPY 事务优先考虑 \copy 或 COPY超大迁移COPY 分区表 并行分批 停业务窗口4. 锁、约束与并发插入的底层逻辑4.1 一条INSERT到底拿了哪些锁很多人在并发插入问题上吃过亏却不太清楚INSERT在PostgreSQL内部到底加了什么锁。简单说有两层数据行上的行级排他锁表上的RowExclusiveLock行排他表锁。这里有个很容易误解的点INSERT需要表级锁但这不代表它阻塞别的INSERT。RowExclusiveLock这种表级锁是兼容多个写入事务并存的它存在的意义主要是防止对表的DDL操作比如DROP、ALTER、TRUNCATE和写入操作并发执行。你可以想象成多个作者可以在各自的行上写内容互不打扰但如果有管理员要把整个书销毁那就必须等所有作者停笔。4.2 唯一索引冲突有时候不是报错而是在等待这是PostgreSQL并发插入里最隐蔽的坑之一。两个事务同时插入相同唯一键时后插入方不会立刻拿到“duplicate key”错误而是先等前一个事务提交或回滚。场景还原事务A先插入了一行unique_key100的数据但还没提交事务B紧接着插入同一个unique_key100的数据。事务B此时在做什么它检测到该键的索引条目正在被另一个未提交事务引用于是它挂起等待事务A结束。如果事务A提交了事务B拿到的是唯一键冲突错误如果事务A回滚了事务B才真正插进去。这解释了为什么很多“高并发下偶发唯一键重复”的报错往往不是真的逻辑重复而是两个请求几乎同时提交其中一个抢到后另一个被迫失败。设计接口时凡是依赖唯一键做防重的都要预期到ON CONFLICT或捕获唯一键冲突异常不能把“不会冲突”当作假设。4.3 死锁的经典场景和预防死锁在INSERT场景里最容易发生在两个事务互相插入对唯一键的占用时。举个具体例子-- 事务1 BEGIN; INSERT INTO accounts (email) VALUES (aexample.com); -- 事务2 同时执行 BEGIN; INSERT INTO accounts (email) VALUES (bexample.com); -- 事务1又执行 INSERT INTO accounts (email) VALUES (bexample.com); -- 等待事务2释放或提交 -- 事务2又执行 INSERT INTO accounts (email) VALUES (aexample.com); -- 等待事务1提交这个时候两边都在等对方数据库死锁检测器很快会介入随机把其中一个事务杀掉并报“deadlock detected”。预防死锁没有银弹最实用的手段是让多个事务以固定顺序处理数据比如先按业务键排序再插入或者给高冲突场景设置合理的锁等待超时避免业务长时间挂起。4.4 约束的检查时机写入一条数据的时候PostgreSQL会检查非空约束、检查约束、外键约束、唯一约束/主键约束。默认情况下这些约束是在每条INSERT语句执行时立即检查的也就是说数据非法语句直接失败。但PostgreSQL有一个其他数据库用得很少的特性——可延期约束CREATE TABLE orders ( id serial PRIMARY KEY, user_id int NOT NULL, ... CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED );设置成DEFERRABLE INITIALLY DEFERRED之后外键校验被推迟到事务提交时一次性检查。这样做的好处是你可以在同一个事务里先插子表再插父表而不违反外键顺序批量写入时也能减少中间检查的开销。代价是事务提交时需要做更多校验工作同时锁持有时间会更长一些。日常小流量系统用默认立即检查就好了别乱加。5. 常见问题与排查实录5.1 插入变得特别慢我从哪里开始查如果你发现某张表的INSERT变慢了别急着优化SQL语法。按顺序查下面这几项是否在等待锁查pg_stat_activity看有没有大量连接卡在insert等待状态索引是否过多每多一个索引INSERT就要多维护一条索引条目很多人为了查询爽建了七八个索引写入自然被拖慢WAL和磁盘如果磁盘IO已经打满INSERT再快也得排队触发器或外部表某些表上挂了触发器每次插入都会额外执行业务逻辑表是否膨胀长期大量更新删除后被dead tuple占着插入时也会受点影响。我记得某次线上服务慢最后定位到是因为某个统计报表表上挂了一个实时同步触发器每次INSERT都触发一次外部接口调用一次要几百毫秒。查到这里的时候大家都懒得看SQL了问题根本不在INSERT本身。5.2 高并发下的主键生成序列还是UUIDPostgreSQL里经典的自增主键是serial或者bigserial底层由序列实现。序列有一个众所周知的特性它的值跳动虽然是有序的但高并发下产生的ID之间可能有空洞——因为事务回滚不会回收序列值。这对业务通常没影响。如果对并发插入落地速度有要求可以考虑sequence的cache参数CREATE SEQUENCE order_id_seq CACHE 500;CACHE 500表示数据库每次预先把500个序列值分配给当前会话。如果进程崩溃这部分值丢失所以ID会出现更大的空洞但换来的是并发下获取序列值的争用大幅下降。另一种思路是UUIDPostgreSQL内置gen_random_uuid()分布均匀、可作为分布式主键但对索引写入随机性更强可能增加B-Tree页分裂带来的写入放大。业务要求不太高的话bigserial加CACHE就够用了需要跨库合并数据再上UUID。5.3 单引号和转义的小坑往PostgreSQL插入包含单引号的字符串时新手总在转义上翻车。默认写法是单引号翻倍-- 插入一个带单引号的字符串 INSERT INTO messages (content) VALUES (Its a PostgreSQL day!);如果字符串里还有反斜杠情况会更复杂。推荐用标准字符串或者美元引用。美元引用是PostgreSQL的特色玩法写起来非常舒服INSERT INTO docs (body) VALUES ($$The content contains single quotes and \backslash$$);使用美元引用时可以用$$、$tag$、$body$等任意标签包裹里面就不用再关心单引号和转义了。对于从外部迁移过来的SQL脚本这是一个能省下大量时间的技巧。5.4 时间、时区和默认值的坑时间字段的默认值看起来很简单但实际坑也不少NOW()和CURRENT_TIMESTAMP返回的是事务开始时间不是当前语句的真实执行时间clock_timestamp()返回真实当前时间适合需要精确记录“这条数据什么时候进来”的场景timestamptz会做时区转换timestamp不会选错类型会导致你看到的“跨时区时间差8小时”问题。如果业务需要记录精确到毫秒的变更时间我建议用timestamptz clock_timestamp()做默认值而不是用NOW()。曾经有业务因NOW()是事务开始时间这个特性在长事务里插入了一批数据结果所有数据的时间戳全是一样的事后排查浪费了不少人力。这类经验写下来就是想让你少走一次弯路。6. 实打实的几条经验写到这里其实INSERT INTO已经不止是“insert into table values”这回事了。最后分享几个我长期维护PostgreSQL实例后沉淀下来的习惯。第一条所有INSERT必须显式列名并且尽量带上RETURNING哪怕当下用不到。多写的十几个字符换来的是半年后不用靠猜去维护。第二条凡是可能重复写入的业务一律用ON CONFLICT设计幂等不要用“先查再插”。查和插之间的窗口是天然存在的竞态条件放到数据库层解决既简洁又可靠。第三条数据导入第一选择永远是COPY不是INSERTINSERT只负责业务写入批量数据导入交给COPY配合好的事务策略效率和可靠性都可以拉满。回头看看这篇文章覆盖的语法、冲突处理、批量性能、锁机制和排障手段都是我在项目中被坑出来的积累。如果你也在用PostgreSQL建议把RETURNING和ON CONFLICT拿到测试库亲手敲一遍感受一下语句返回和冲突跳过的真实行为。遇见INSERT的问题别怕底层机制理解了写进去的自然也就稳了。
阅读完成 · 觉得有帮助？
