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取默认值 0stock取默认值 0updated_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 ... SELECTINSERT 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已经存在这次插入直接跳过不会报错。这个写法不需要指定冲突目标任何唯一约束冲突都会生效。更高级的是冲突后转更新也就是真正意义上的 upsertINSERT 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.00EXCLUDED.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 删除数据基础语法、级联删除与 TRUNCATE4.1 基础 DELETE 与外键行为删除语句的写法很简单DELETE FROM order_items WHERE id 10;WHERE条件也是重点。不带条件的DELETE FROM 表名会把整张表清空执行前必须三思。有些初学朋友在 psql 里手滑执行了无条件的删除只能靠备份恢复很痛苦。删除时同样可以带RETURNINGDELETE 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 CASCADECREATE 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。两者最大的区别是执行机制完全不同我列一个对比方便你理解对比项DELETETRUNCATE语句类型DML逐行删除DDL直接重建存储结构执行速度慢数据量大时尤其明显极快不逐行处理是否可回滚可以在事务内可回滚可以PostgreSQL 中 DDL 支持事务回滚是否逐行触发触发器会触发不触发空间释放不立即释放需要 VACUUM立即释放外键引用受外键约束影响被其他表引用时无法直接执行我举个最常见的场景如果你只是想清空一张日志表并且这张表没有被其他表引用用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 的读写互不阻塞读数据的事务不会阻塞写事务写事务也不会阻塞读事务。但两个写事务同时更新同一行时依然会互相竞争。假设有两个会话会话 ABEGIN; UPDATE products SET stock stock - 1 WHERE id 1;执行后不提交。会话 BUPDATE 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_activitySELECT 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 相关报错翻来覆去就那几种整理成一张速查表报错信息原因解决方案column xxx does not exist列名写错或用双引号包住了字段值检查列名字段值用单引号避免创建混合大小写列名null value in column price violates not-null constraint插入 NULL 到有 NOT NULL 约束的列检查输入参数或给列加 DEFAULTduplicate key value violates unique constraint唯一约束冲突使用 ON CONFLICT DO NOTHING / DO UPDATEthere is no unique or exclusion constraint matching the ON CONFLICT specificationON CONFLICT 指定的目标不是唯一约束确认冲突目标对应表上的唯一索引或约束update or delete on table orders violates foreign key constraint被其他表引用阻止删除先删子表数据或给外键加 ON DELETE CASCADEdeadlock detected两个事务互相持有对方需要的资源统一多表更新顺序缩短事务时间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 把整张表的锁拖住。如果你能把这些习惯从入门阶段就刻进肌肉记忆后面做项目遇到数据一致性和性能问题时会少踩很多坑。