PostgreSQL增删改实战：RETURNING与ON CONFLICT

1. 这一篇到底要解决什么问题

这是 PostgreSQL 系列教程的第 8 篇，专门讲插入、更新与删除数据。如果你之前只写过最简单的INSERT INTO ... VALUES，后面的RETURNING、ON CONFLICT、DO UPDATE这些东西肯定能帮你打开新世界的大门。

先说句实在话：PostgreSQL 的增删改语法，表面上和 MySQL、Oracle 差不太多，但骨子里是完全不同的思路。PostgreSQL 有自己的一套数据版本管理机制（MVCC），所以它的UPDATE执行路径和锁定行为、DELETE之后的磁盘回收方式，都跟你想的不太一样。如果不知道这些底层机制，后面遇到“更新锁等待”“删除后空间没变小”这种问题，你会一头雾水。

另外，新版本的 PostgreSQL 16 在性能、逻辑复制、锁管理上都有不少改进，写 DML 时的体验更稳。这篇文章就是以 PostgreSQL 16 为基准，从建表开始，把插入、更新、删除三块语法全部过一遍，再配合真实业务场景讲透实战技巧。适合刚入门 PostgreSQL 的新手，也适合从 MySQL 转过来的开发者，文中所有示例都可以直接在你的本机环境里跑一遍。

我在讲每一个语法点时，都会解释一个关键问题：为什么 PostgreSQL 要这么设计，以及你在实际项目中应该怎么用。

1.1 PostgreSQL 增删改的三大特色

第一是RETURNING子句。执行完INSERT、UPDATE、DELETE之后，PostgreSQL 可以直接把受影响的行返回来，你不需要再查一次数据库就能拿到新生成的主键或者旧值。这个能力在做接口开发时特别好用。

第二是对冲突处理的支持。ON CONFLICT可以在插入遇到唯一键冲突时选择忽略或者转为更新操作，很多人叫它“upsert”。这在同步数据、防止重复插入的场景里非常实用。

第三是 MVCC 多版本并发控制。每个事务修改数据时，不是直接覆盖旧数据，而是生成一个新版本。这个机制保证了读写互不阻塞，但也带来一个副作用：旧版本数据需要被清理，所以DELETE大量数据后，磁盘空间不会立刻释放。

1.2 环境准备和示例表

本机装好 PostgreSQL 16，用psql命令行或者 DBeaver 都可以。我习惯用psql做演示，因为执行计划、事务状态看得比较清楚。

下面这几张表是整个教程的公用示例：

-- 商品表 CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, price NUMERIC(10,2) NOT NULL DEFAULT 0, stock INT NOT NULL DEFAULT 0, updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单主表 CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_name VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT '已创建', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单明细表 CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id), product_id INT NOT NULL REFERENCES products(id), qty INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL );

注意我在products.name上加了一个UNIQUE约束，这是为了后面演示ON CONFLICT时能直接命中冲突目标。

2. INSERT 插入数据：从入门语法到批量操作

2.1 最基础的 INSERT 语法

PostgreSQL 的插入语法基本遵循 SQL 标准，但细节里有些坑。先看最常见的写法：

INSERT INTO products (name, price, stock) VALUES ('机械键盘', 399.00, 50);

指定了列名，值和列一一对应，这是推荐写法。如果不想写列名，也可以写成INSERT INTO products VALUES (1, 'xxx', ...)，但这样你得精确记住表结构的列顺序，表结构一变就容易出错。我在项目里从来不这么写。

还有一个容易被忽略的地方：字符串用的是单引号，不是双引号。双引号在 PostgreSQL 里表示“标识符”，也就是列名和表名。如果你给字段值套上双引号，PostgreSQL 会尝试把它当成列名解析，然后报出column "xxx" does not exist的错误。

插入时可以只填部分列，其他列就用默认值：

INSERT INTO products (name) VALUES ('鼠标垫');

这条 SQL 中，price取默认值 0，stock取默认值 0，updated_at取当前时间。默认值是建表时用DEFAULT定义的，如果你没有定义默认值，该列又允许为空，那就插入了NULL。

再补充一个细节：VALUES子句后面的值列表可以省略列名，但如果某列是NOT NULL且没有默认值，插入时不写这一列就会直接报错。所以建表时把默认值设计好，插入代码会清爽很多。

2.2 批量插入和 INSERT INTO SELECT

批量插入多行，直接扩展VALUES列表：

INSERT INTO products (name, price, stock) VALUES ('显示器 27 寸', 1299.00, 20), ('无线鼠标', 129.00, 100), ('笔记本支架', 189.00, 60);

一次插入 3 行，数据库只解析一次 SQL，性能比逐条插入好得多。我实测过，几千行的数据在这个量级上性能差异还不明显，但如果到几十万行，单条VALUES批量插入和循环逐条插入的差距会有几十倍。

如果要把一张表的数据搬到另一张表，或者从查询结果里生成数据，就要用INSERT INTO ... SELECT：

INSERT INTO products_archive (id, name, price, stock) SELECT id, name, price, stock FROM products WHERE created_at < '2024-01-01';

这个写法把查询结果直接作为插入的数据源，常用于建历史表、报表中间表。注意它不会帮你处理主键冲突，如果目标表已经有相同主键，会报错。

2.3 RETURNING 子句：插入后直接拿数据

这是 PostgreSQL 非常亮眼的功能。插入一条记录后马上拿到它的主键和整行数据：

INSERT INTO products (name, price, stock) VALUES ('USB-C 扩展坞', 239.00, 80) RETURNING id, name, stock;

执行结果会直接返回一列数据，比如id = 4。在后端接口里，你不需要再执行一条SELECT去反查这个新生成的主键。很多 ORM 框架底层也是靠RETURNING实现的“插入后立即回填 ID”功能。

RETURNING后面可以写具体字段名，也可以写*返回整行：

INSERT INTO products (name, price, stock) VALUES ('便携支架', 99.00, 200) RETURNING *;

注意：RETURNING返回的是插入后的真实数据，包括默认值、触发器修改后的值。如果你在表上建了自动更新时间戳的触发器，RETURNING *拿到的是更新后的时间戳，而你在VALUES里写得再准也没用。

2.4 ON CONFLICT：解决唯一键冲突的利器

向带唯一约束的表插入数据时，最烦人的就是“重复插入报错”。传统做法是先查一遍再决定插不插，但并发高的时候，先查再插依然会有竞态问题。PostgreSQL 的ON CONFLICT就是为这个场景设计的。

最简单的用法是冲突后什么都不做：

INSERT INTO products (name, price, stock) VALUES ('机械键盘', 399.00, 50) ON CONFLICT DO NOTHING;

如果name已经存在，这次插入直接跳过，不会报错。这个写法不需要指定冲突目标，任何唯一约束冲突都会生效。

更高级的是冲突后转更新，也就是真正意义上的 upsert：

INSERT INTO products (name, price, stock) VALUES ('机械键盘', 429.00, 60) ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price, stock = EXCLUDED.stock, updated_at = now() RETURNING id, price, stock;

这里有两个关键点必须讲清楚。

第一，ON CONFLICT (name)后面的name是冲突目标，它必须对应表上的一个唯一索引或唯一约束。如果表上有多个唯一键，而你这里写错了目标，PostgreSQL 会直接报错：there is no unique or exclusion constraint matching the ON CONFLICT specification。

第二，EXCLUDED表示“本次想插入但没插进去的那行数据”。在这个例子里，EXCLUDED.price就是 429.00，EXCLUDED.stock就是 60。如果既有数据的价格要更新成新价格，直接用EXCLUDED引用就行。

这里还有一个容易翻车的点：DO UPDATE子句会执行真实的更新操作，因此会触发行锁、触发更新触发器、产生新版本数据。如果表的更新频率很高，冲突更新可能比单纯插入慢很多。做海量数据导入时，如果大量数据都冲突，upsert 的性能压力会比DO NOTHING大不少，要根据业务情况取舍。

3. UPDATE 更新数据：语法、条件更新与批量更新

3.1 UPDATE 基础语法

更新语句的基本结构是：

UPDATE products SET price = 449.00, stock = stock - 10 WHERE id = 1;

这里要注意stock = stock - 10这种写法。在更新同一行数据时，SET右边的表达式是同时基于这行的旧值计算的，不会互相覆盖。举个例子：

UPDATE products SET price = price * 1.1, stock = stock - 10 WHERE id = 1;

price和stock的取值都基于更新前的旧值，不管SET里写了多少个字段，都不会出现“先更新了 price，再拿更新后的 price 去算 stock”的情况。这和某些数据库的顺序求值逻辑不一样，PostgreSQL 的做法更直观，也更安全。

不带WHERE条件的UPDATE会更新整张表，请务必确认条件写对了。我见过有人写脚本时漏了WHERE，直接把线上产品表的价格全部改掉了，还好有备份，否则就是事故。所以在生产环境执行更新前，我会习惯性地先跑一条等价的SELECT看看会影响多少行。

3.2 用 CASE 实现同表条件批量更新

业务里经常有“按不同条件把某一列更新成不同值”的需求。比如商品要根据类别批量调价，新手最容易写成循环，一条一条执行UPDATE。更高效的做法是每行根据条件算出新值，一次UPDATE全部完成：

UPDATE products SET price = CASE WHEN category = '外设' THEN price * 1.2 WHEN category = '耗材' THEN price * 1.1 ELSE price END;

如果没有category字段，按名字匹配也是一样的道理。CASE是逐行判断的，所以性能上只扫描一次表，更新逻辑集中在一条 SQL 里，也方便回滚和审计。

我实测过一个真实场景：用 Python 逐条执行UPDATE更新 3 万条价格数据，总耗时接近 40 秒；改成一条带CASE的 SQL 之后，耗时降到 1 秒以内。差距来自每条 SQL 都要经历完整的解析、优化、执行过程，而一条CASE更新只需要一次。

3.3 多表更新：UPDATE FROM

标准 SQL 里的UPDATE一般只能更新一张表，但 PostgreSQL 允许你通过FROM子句引入其他表，用其他表的字段来更新目标表。

举个例子，订单明细里的单价可能在下单那一刻就已经确定了，但如果想要按最新商品价格重新计算，可以用商品表来更新明细表：

UPDATE order_items oi SET unit_price = p.price FROM products p WHERE oi.product_id = p.id;

写这条 SQL 时有几个细节需要注意。第一，目标表最好起个别名，这里的oi是目标表别名。第二，FROM子句里引入的表，可以出现在WHERE条件中做关联。第三，如果FROM表里有多行数据都匹配目标表的同一行，PostgreSQL 更新结果是不确定的。所以做多表关联更新前，要确认关联字段在来源表里是唯一的。

多表更新的典型场景是“从临时表回刷主表数据”：

UPDATE products p SET stock = p.stock + s.delta FROM temp_stock_adjustment s WHERE p.id = s.product_id;

这种写法比逐条循环更新高效得多，因为一次 UPDATE 完成全部关联和更新操作，应用层不需要管循环逻辑。结合临时表使用，还可以把复杂的计算逻辑放到数据库里完成。

3.4 UPDATE 的 RETURNING

和插入一样，更新操作也能用RETURNING返回更新后的行数据：

UPDATE products SET stock = stock - 1, updated_at = now() WHERE id = 1 RETURNING id, name, stock;

这条 SQL 返回更新后的id、name、stock。在业务里很有用，比如扣减库存后，直接拿到剩余库存值返回给前端展示，不用再查一次。这里甚至可以返回更新前的旧值：

UPDATE order_items SET qty = qty + 1 WHERE id = 10 RETURNING qty, unit_price;

RETURNING返回的是新的qty值。如果你需要记录“变更前是多少”，就得在应用层先查询旧值，或者用触发器记录。PostgreSQL 还不支持在RETURNING里直接引用 OLD/NEW。

4. DELETE 删除数据：基础语法、级联删除与 TRUNCATE

4.1 基础 DELETE 与外键行为

删除语句的写法很简单：

DELETE FROM order_items WHERE id = 10;

WHERE条件也是重点。不带条件的DELETE FROM 表名会把整张表清空，执行前必须三思。有些初学朋友在 psql 里手滑执行了无条件的删除，只能靠备份恢复，很痛苦。

删除时同样可以带RETURNING：

DELETE FROM order_items WHERE id = 10 RETURNING id, product_id, qty;

返回的是被删除行的数据，这在做日志记录或审计时非常方便。

外键约束对DELETE的影响需要特别注意。我们示例里的order_items引用了orders.id和products.id，如果你尝试删除一个还存在明细的订单主表记录：

DELETE FROM orders WHERE id = 1;

PostgreSQL 会直接报外键约束错误。此时要么先删明细，要么在外键定义时加上ON DELETE CASCADE：

CREATE TABLE order_items ( ... order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id INT NOT NULL REFERENCES products(id) ON DELETE RESTRICT );

我的习惯是：主从关系的子表用ON DELETE CASCADE让主记录删除时自动带走明细；但商品被订单引用时用RESTRICT，防止误删有历史订单的商品。

4.2 用 USING 做多表删除

DELETE同样支持关联其他表来限定删除范围。比如要删除所有已下架商品的订单明细：

DELETE FROM order_items oi USING products p WHERE oi.product_id = p.id AND p.stock = 0;

这里的USING相当于UPDATE FROM的删除版本。WHERE条件里既写关联条件，也写筛选条件。这种多表删除特别适合清理历史脏数据。

4.3 DELETE 和 TRUNCATE 应当如何选择

清空一张表，很多人会纠结用DELETE FROM还是TRUNCATE。两者最大的区别是执行机制完全不同，我列一个对比方便你理解：

我举个最常见的场景：如果你只是想清空一张日志表，并且这张表没有被其他表引用，用TRUNCATE是首选，因为快。但如果这张表要被DELETE的RETURNING拿回数据做审计，或者有触发器要执行，那就用DELETE。

另外，TRUNCATE在 PostgreSQL 里执行时会拿到表的ACCESS EXCLUSIVE锁，这是一个重量级锁，在此期间该表上的其他操作都会被阻塞。所以生产环境最好不要在大白天直接TRUNCATE业务表。

5. 事务与并发：理解增删改背后的 MVCC 机制

5.1 为什么要把多个 DML 放进一个事务

业务里的数据操作很少是单条 SQL 完成的，比如“用户下单”这个动作，至少涉及三件事：插入订单、插入明细、扣减库存。如果其中某一步失败，前面几一步已经执行了，数据就会不一致。

PostgreSQL 的标准做法是开一个事务：

BEGIN; INSERT INTO orders (customer_name) VALUES ('张三') RETURNING id; INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (1, 2, 1, 129.00); UPDATE products SET stock = stock - 1 WHERE id = 2; COMMIT;

事务内的所有操作要么全部成功，要么全部失败回滚。这是数据库保证数据一致性的核心机制。

使用事务时，有几个实际经验需要记住。第一，事务不要开得太长，尽量把耗时操作放在事务外，事务里面只放数据库操作。第二，在 psql 里执行BEGIN后，如果中途反悔，执行ROLLBACK可以撤销所有未提交操作。第三，DBeaver 这类图形工具有时候会自动开启事务，你执行完 SQL 后没有点提交，锁会一直占着，这个问题后面讲锁的时候还会遇到。

5.2 更新数据的机制：UPDATE 其实是 DELETE + INSERT

很多从 MySQL 转过来的朋友不理解为什么 PostgreSQL 更新一行数据会那么慢，也不理解为什么删除数据后磁盘空间没变小。这背后的核心是 MVCC。

PostgreSQL 更新一行数据时，并不是直接修改原来的数据所在的位置，而是逻辑上把旧行标记为不可见，同时在表里插入一个新版本的行。也就是说一次UPDATE内部等价于“删除旧版本 + 插入新版本”。这就是为什么更新频繁的表会快速膨胀，因为旧版本数据还留在表文件里，需要后续的VACUUM才能清理。

同样地，执行DELETE时，数据行并没有真的从磁盘文件里消失，只是被标记为“已删除”。如果你删除了 100GB 的数据，随后查看磁盘空间，你会发现文件并没有变小。这是正常的，需要执行VACUUM FULL或者等待自动清理进程处理好之后，空间才会被释放。

明白这个机制之后，你就知道在设计表结构时要尽量避免频繁更新大字段，能用追加写就用追加写。日志类、流水类数据尽量只插入不更新，否则表膨胀会非常快。

5.3 行锁与锁等待

因为 MVCC 的存在，PostgreSQL 的读写互不阻塞：读数据的事务不会阻塞写事务，写事务也不会阻塞读事务。但两个写事务同时更新同一行时，依然会互相竞争。

假设有两个会话：

会话 A：

BEGIN; UPDATE products SET stock = stock - 1 WHERE id = 1;

执行后不提交。

会话 B：

UPDATE products SET stock = stock - 1 WHERE id = 1;

会话 B 会一直等待，直到会话 A 执行COMMIT或ROLLBACK。这个等待可能是无限期的，因为 PostgreSQL 默认没有设置锁等待超时。

所以在实际项目里，推荐设置一个lock_timeout，防止一条 SQL 无限卡住。在 PostgreSQL 配置文件postgresql.conf或当前会话里都可以设置：

SET lock_timeout = '5s';

生产环境我一般会设置为 5 到 10 秒。这样一来，如果某个会话长时间持锁不释放，另一个会话会在超时后报错，而不是无脑挂起。

排查锁等待问题时，最常用的视图是pg_stat_activity：

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active';

看到wait_event_type = Lock的记录，再结合pg_locks查具体的锁对象，很容易定位到是哪个事务堵住了后面的操作。

6. 常见报错与避坑速查

6.1 高频报错对照表

我在群里答疑时，遇到的 DML 相关报错翻来覆去就那几种，整理成一张速查表：

deadlock detected这个报错尤其值得说。它通常发生在两个事务以不同顺序更新多张表时。比如事务 1 先更新 A 表再更新 B 表，事务 2 先更新 B 表再更新 A 表，两者就会互相等待形成死锁，PostgreSQL 会自动检测并回滚其中一方。解决办法是让所有事务都以相同的顺序访问表，这是工程规范问题，不是单靠 SQL 能解决的。

6.2 大表删除和批量更新的实战经验

删除大表数据不要在一条 SQL 里一次删完。一方面锁持有时间太长，会长时间阻塞其他业务；另一方面产生的死元组数量巨大，后续自动清理压力也很大。

建议分批删除，比如每次删 5000 行，循环执行：

DELETE FROM orders WHERE id IN ( SELECT id FROM orders WHERE status = '已取消' AND created_at < '2022-01-01' ORDER BY id LIMIT 5000 );

在应用层循环执行，直到受影响行数小于 5000 为止。每次删除之间隔几秒，给后台清理任务留出处理时间，对在线业务影响小很多。

批量更新的实战技巧是不要用循环，能用一条 SQL 绝不分多条。目标表的数据要按一只临时表或子查询的结果来更新时，用UPDATE ... FROM会比逐条更新快几个数量级。我在前面的章节里已经给出过示例：先把要调整的增量数据落到临时表，再一次性关联更新主表。这个方法值得写进你的代码模板里。

6.3 我在实际项目中养成的几个小习惯

第一，所有写操作的 SQL 先跑SELECT看影响范围，再执行更新或删除，这个习惯救过我很多次。第二，在 psql 里练习时，先开事务，执行完不急着提交，先看一眼结果，确认无误再COMMIT，有问题就ROLLBACK。第三，给生产环境的数据库账号做权限分级，业务账号只给INSERT/UPDATE/DELETE权限，不给DROP/TRUNCATE权限，防止手滑。

另外还要提醒一点：如果你在用 DBeaver 或者 Navicat 这类图形工具，注意它们可能默认开启自动提交，也可能默认关闭，两种状态下你在界面里执行 DML 的行为完全不同。建议操作前先看一下工具栏上“自动提交”按钮的状态，避免出现“我以为提交了其实没有，表锁一直没释放”的情况。

PostgreSQL 16 的 DML 语法，整体上依然保持“标准中带有自己特色”的路线。RETURNING和ON CONFLICT这两个特性，一旦用顺手，你会觉得写数据接口比其他数据库舒服很多。我个人最常用的组合是：插入时用ON CONFLICT DO UPDATE配合RETURNING id，一条 SQL 同时完成“存在就更新、不存在就插入、最后拿回主键”三个需求；更新库存时用UPDATE ... FROM结合临时表，批量处理几万条数据秒级完成；删除数据时宁可分批做慢一点，也不让一条大 SQL 把整张表的锁拖住。

如果你能把这些习惯从入门阶段就刻进肌肉记忆，后面做项目遇到数据一致性和性能问题时，会少踩很多坑。