1. 为什么我建议先把增删改彻底吃透1.1 这篇教程的定位与前置基础说句实在话PostgreSQL 入门最容易被高估的是 SELECT最容易被低估的是 INSERT、UPDATE、DELETE 这一组增删改操作。SELECT 写错了大不了重查一次但插入、更新、删除这三条语句写偏了轻则把数据改花重则把线上记录删完。这篇文章是我这套 PostgreSQL 16 入门教程的第 8 篇重点就是把插入、更新、删除三条语法线完整拆开从最基础的写法一路讲到 RETURNING、ON CONFLICT、UPDATE ... FROM、TRUNCATE 这些进阶细节最后再配一套订单管理的实战案例保证你能照着敲一遍就上手。看到这篇教程的读者建议先把前面几篇关于数据库安装、建表、SELECT 与条件查询的内容过一遍。倒不是说没学过就不能看而是增删改逃不开表结构、主键、字段类型这些前置概念没有基础直接看会有点懵。如果你已经能在 psql 里建出表、写出带 WHERE 的 SELECT那这篇就是给你准备的。1.2 三条语句的整体心法我用了这么多年 PostgreSQL对增删改只有一句总结INSERT 负责把数据带进来UPDATE 负责把状态改对DELETE 负责把错误的和过期的数据清掉。三句话看着简单但每句话背后都有一堆细节。INSERT 要考虑默认值、主键冲突、批量插入的性能UPDATE 要考虑 WHERE 条件会不会把不该改的行也改了DELETE 要考虑外键关系、TRUNCATE 的取舍、删除后序列是否重置。就拿 WHERE 条件这件事来说很多新手写 UPDATE 和 DELETE 都习惯先写完再想条件这是我最不推荐的顺序。正确做法永远是先在事务里用一条 SELECT 验证你的 WHERE 条件命中哪些行确认无误后再执行写操作。道理很简单SELECT 错了可以再查UPDATE 错了就只能靠备份恢复。后面所有章节我都会反复强调这个纪律因为它比任何语法细节都重要。2. INSERT 语法详解把数据可靠地带进来2.1 基础 INSERT 与列清单的讲究PostgreSQL 里最基本的插入语句长这样INSERT INTO student (id, name, score) VALUES (1, 张三, 88.5);这行 SQL 的逻辑很直白往 student 表的 id、name、score 三个字段里塞入对应的三个值。需要注意的是字段清单的顺序可以和表结构定义顺序不同只要值和字段一一对应就行。比如你把字段写成 (name, score, id)值也要跟着写成 (张三, 88.5, 1)PostgreSQL 会按你给定的顺序对应不会自作主张帮你对齐。如果省略字段清单直接写成INSERT INTO student VALUES (1, 张三, 88.5);那么值的顺序就必须完全匹配表定义的字段顺序。这种写法日常开发我也用但有一个致命弱点一旦表结构调整了字段顺序这段 SQL 就废了。所以我的习惯是永远写明字段列表哪怕多打几个字换来的却是可读性和稳定性。插入时没写到的字段会自动使用默认值没有默认值且允许 NULL 的字段就是 NULL如果字段既不允许 NULL 又没有默认值PostgreSQL 会直接报 not-null 约束错误。2.2 多行插入与批量插入的取舍一次插入多行数据是 PostgreSQL 非常实用的能力语法就是把 VALUES 部分用逗号连接多个括号INSERT INTO student (id, name, score) VALUES (2, 李四, 91.0), (3, 王五, 77.5), (4, 赵六, 85.0);这种写法比逐条执行 INSERT 快得多因为它把多次网络往返压缩成一次。批量插入时我建议把单条语句的 VALUES 控制在 500 到 1000 组左右而不是无脑塞一万行。原因有两个一是单条 SQL 太长在排查问题时很痛苦二是 PostgreSQL 对绑定参数数量有限制虽然这个限制在实战中很少触顶但分块插入能让你在出错时更容易定位是哪一批数据出了问题。如果你是从 CSV 或者程序里导海量数据那么 INSERT 多行还真不是最优解。PostgreSQL 的COPY命令才是灌大批量数据最快的方案COPY table FROM /path/to/file.csv WITH (FORMAT csv, HEADER true);这种写法在数据迁移场景下几乎是标配。但这是另一个话题等后面讲到数据导入导出时我再展开。2.3 RETURNING让数据库把结果还给你很多人插入完数据后第一反应是再查一次数据库拿到主键或者默认值。在 PostgreSQL 里完全没必要RETURNING子句就是干这个的INSERT INTO student (name, score) VALUES (钱七, 92.5) RETURNING id, name, created_at;执行之后PostgreSQL 会直接返回插入的这行数据的 id 和其他字段不用你再发一条 SELECT。这在 Web 后端里特别有用比如用户注册后要立刻拿到新用户的自增主键或者订单创建后要立即回显订单号一条 INSERT 加 RETURNING 就搞定了还减少了竞态风险。需要提醒的是RETURNING 可以返回任意字段和表达式不只是 id。比如RETURNING id, score * 0.95 AS after_discount这在验证插入逻辑时非常好用。我在写自动化测试时就经常用 RETURNING 把插入结果直接作为断言目标省去二次查询的麻烦。2.4 ON CONFLICT处理唯一键冲突的实战姿势真实业务里插入时最怕碰到的就是唯一约束冲突比如用户重复注册、订单号重复生成。PostgreSQL 的ON CONFLICT就是为这种场景准备的INSERT INTO student (id, name, score) VALUES (5, 周八, 89.0) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name, score EXCLUDED.score;这里有两个关键点要解释清楚。第一个是ON CONFLICT (id)括号里必须写实际存在的唯一索引或主键列PostgreSQL 要靠它判断冲突第二个是EXCLUDED这个关键字它代表的是如果不冲突、本来要插入的那行数据。上面这条语句的意思就是如果 id5 不存在就插入存在就把名字和成绩更新成新值这就是常说的 upsert。如果你只是想让重复数据静默跳过可以写ON CONFLICT (id) DO NOTHING适合埋点、日志这类允许丢数据的场景。我在实战中踩过一个坑ON CONFLICT只有在冲突列上确有唯一索引或主键时才生效如果列上只有普通索引PostgreSQL 会报错说没有匹配的冲突目标。所以建表时想清楚哪些列必须唯一这个决定越早做越好。3. UPDATE 语法详解把状态精准地改对3.1 基础 UPDATE 与 WHERE 的纪律UPDATE 的语法骨架极其简单UPDATE student SET score 90.0 WHERE id 5;但简单和安全是两回事。只要 WHERE 忘了写或者写宽了整张表都会被改掉。我见过不只一次生产事故就是因为一条 UPDATE 少了一个条件把所有用户的状态全部改成了同一值。所以我把 UPDATE 和 DELETE 的第一条纪律放在一起讲执行前先在事务里用相同条件查一遍 SELECT。BEGIN; SELECT * FROM student WHERE id 5; -- 先确认命中哪一行 UPDATE student SET score 90.0 WHERE id 5; COMMIT;用事务包裹的意义在于如果发现 UPDATE 影响的行数不对可以立刻 ROLLBACK不会留下任何痕迹。另外养成一个习惯在 UPDATE 后用RETURNING或GET DIAGNOSTICS检查实际影响行数这能帮你第一时间发现条件漂移。3.2 UPDATE ... FROM关联其他表一起改单表 UPDATE 谁都会但现实业务经常需要根据另一张表的数据来更新当前表。比如要根据客户等级更新订单折扣这时候就要用UPDATE ... FROMUPDATE orders o SET discount_rate c.discount_rate FROM customers c WHERE o.customer_id c.id AND c.level VIP;这条语句的意思是从 customers 表里找到所有 VIP 客户然后把他们的折扣率同步到 orders 表对应订单上。这里最容易犯的错是把FROM当成了额外要 SET 的表其实不是。FROM 后面跟的是关联数据源真正的更新目标永远只有 UPDATE 关键字后面的那张表。另外一个细节是 WHERE 条件的配对关系要写好。如果 orders 表里有多个订单对应同一个 customer_id那么这些订单都会一起被更新这是符合预期的。但如果你只想更新每个客户最近的一笔订单那就得用子查询把目标限定出来。类似这种只更新符合条件的部分行的需求我建议先在 SELECT 里把 JOIN 条件验证一遍确认关联后命中的行数无误再套进 UPDATE。3.3 SET 表达式的求值顺序与慎用场景PostgreSQL 的 UPDATE 在给多个字段赋值时是允许后面的表达式引用前面已经被更新的字段值的。比如UPDATE student SET score score 5, adjusted score * 0.9;这里adjusted拿到的是score加 5 之后的新值因为 PostgreSQL 按 SET 列表从左到右求值。这个特性跟标准 SQL 的所有表达式都基于原始行值求值不一样属于 PostgreSQL 自己的行为。多数时候这个特性很方便但我还是建议在写这种连锁赋值时多留个心眼因为团队里如果有人习惯了 MySQL 或 Oracle 的行更新语义很容易产生分歧。最好的做法是在字段少的情况下刻意写成互不依赖的表达式从源头上避免歧义。4. DELETE 语法详解把数据删得干净且安全4.1 基础 DELETE 与 RETURNING 的配合DELETE 的语法比 INSERT 和 UPDATE 更简单但也更危险DELETE FROM student WHERE id 5 RETURNING id, name;同样地WHERE 是唯一能保护你的东西。省略 WHERE 就意味着清空全表这是 DBA 最忌讳的操作姿势。我在所有内部培训里都会讲一个原则DELETE 语句永远先配 WHERE哪怕你确实想清空整张表也应该明确用 TRUNCATE 而不是空 DELETE。DELETE 加 RETURNING 的好处是能拿到被删掉的数据。这在对账、审计、失败回补场景里很有用。比如删除一批过期订单后用 RETURNING 把删除结果落一份日志万一后来发现问题至少知道哪些数据没了、什么时候没的。DELETE 不会像物理删除文件那样从磁盘上彻底抹掉数据它只是给行打上删除标记后续的 VACUUM 才会真正清理空间这个机制后面讲维护时再细说。4.2 DELETE 与 TRUNCATE 到底怎么选很多初学者分不清 DELETE 和 TRUNCATE 的区别这里我给出一张对比表专门讲清楚它们各自的适用场景对比维度DELETETRUNCATE删除粒度按 WHERE 条件删可指定行只能整表清空事务回滚完全支持事务回滚支持事务回滚但锁粒度更重自增序列不会重置序列号会重置自增序列行级触发器会触发每行的触发器不触发行级触发器执行速度逐行处理相对慢整表快得多适用场景删特定数据、日常清理清空整表、重置测试环境如果你的需求就是把这张表全部数据清掉并且序列号从 1 重新开始TRUNCATE 显然更合适。但要注意 TRUNCATE 会直接重置自增主键的序列如果你正打算清空后再倒入一批保留原 id 的数据就要额外处理序列问题。反过来如果你只删一部分数据哪怕删的是 99%也别贪图 TRUNCATE 的速度老老实实写 DELETE否则会把不该删的都删掉。4.3 外键约束下的删除策略PostgreSQL 默认的删除策略是 RESTRICT意思是如果别的表还有记录引用你正要删的这一行删除会直接报错防止你制造孤儿数据。比如 customers 表有订单引用时直接DELETE FROM customers WHERE id 1;会报外键违反错误这是数据库保护你的方式。设计表结构时就要想清楚删除策略ON DELETE CASCADE表示删父表时自动删子表记录适合订单明细这种随主表消亡的数据ON DELETE SET NULL表示删除时把外键字段置空适合删除用户但保留历史订单的场景。我自己的经验是尽量不要依赖 CASCADE 去隐式删数据因为级联删除的影响范围是隐性的线上排查问题时非常难追查。真要删父子关联的数据我宁可分两步先查子表影响范围再显式删除子表记录可控性高得多。5. 实战案例一套订单数据从插入到清理的完整演练5.1 设计基础表结构纸上谈兵没意思这一节我带你从头到尾做一个订单场景。先建四张表客户、商品、订单、订单明细。为了让例子贴近 PostgreSQL 16 的现代写法我用generated always as identity代替传统的 serial 自增列。CREATE TABLE customers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, level text NOT NULL DEFAULT normal, balance numeric(10,2) NOT NULL DEFAULT 0 ); CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL ); CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(id), status text NOT NULL DEFAULT pending, total_amount numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE order_items ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id bigint NOT NULL REFERENCES products(id), quantity integer NOT NULL DEFAULT 1 CHECK (quantity 0), line_total numeric(10,2) NOT NULL DEFAULT 0 );先解释两个设计点。第一orders 表外键引用了 customers删除客户默认受 RESTRICT 保护防止删了客户但留下孤儿订单。第二order_items 的外键用了 ON DELETE CASCADE因为订单一旦删除明细没有独立存在的意义。这两个策略一个保守一个激进正好演示前面讲过的不同取舍。5.2 插入基础数据先把客户和商品灌进去。因为 id 是自动生成的我只提供业务字段插入后立刻用 RETURNING 拿回 idINSERT INTO customers (name, level, balance) VALUES (张三, VIP, 1000.00) RETURNING id, name;对普通客户我也同样插入两个接着插入三件商品INSERT INTO products (name, price) VALUES (机械键盘, 399.00), (无线鼠标, 159.00), (显示器支架, 249.00) RETURNING id, name, price;RETURNING 批量返回时会把三行都打出来正好可以确认三条数据的 id 是否是 1、2、3。如果你用的是 serial 或 identity 序列批量插入后想拿回的 id 会有多条一般就是在业务代码里逐条返回或者干脆一个订单明细一次 INSERT。5.3 创建订单、更新状态与算总额接下来我把张三的第一笔订单写进去明细里买两把机械键盘、一个鼠标再通过 UPDATE 把订单总额根据明细汇总算出来。这一步是 INSERT 和 UPDATE 配合的典型场景INSERT INTO orders (customer_id, status) VALUES (1, pending) RETURNING id; INSERT INTO order_items (order_id, product_id, quantity, line_total) VALUES (1, 1, 2, 798.00), (1, 2, 1, 159.00);然后更新订单状态并汇总金额UPDATE orders SET total_amount ( SELECT sum(line_total) FROM order_items WHERE order_id 1 ) WHERE id 1 RETURNING id, status, total_amount;这条 UPDATE 的核心就是子查询从 order_items 表里把订单 1 的明细金额求和写回 orders 的 total_amount。这样能保证总额永远来自明细人工手填迟早出错。如果你在后面继续增加明细别忘了同步更新总额或者干脆建一个触发器自动维护。对这个例子来说手动同步在数据量小时可以接受但上了生产环境后一定要重新评估。5.4 清理演示数据与确认影响范围有些订单是用户误操作产生的测试数据比如 id 为 3 的订单状态是 cancelled现在要从表里清掉。先查这个订单有没有明细确认删除影响范围SELECT * FROM order_items WHERE order_id 3;假设有一条明细我可以利用 order_items 表上的 ON DELETE CASCADE直接删除订单主表让明细跟着一起消失DELETE FROM orders WHERE id 3 RETURNING id, status, total_amount;执行后再查 order_items你会看到订单 3 的明细已经被自动清掉。这就是 CASCADE 的实际效果。但同时要注意这条 DELETE 因为外键存在如果订单 3 还有更细的子表引用级联会一路往下走影响范围就会扩大。生产环境里我强烈建议在 DELETE 之前用一条子查询把所有可能被级联删除的明细数据先备份导出或者把外键策略在文档里写清楚避免操作完才发现漏了审计。6. 常见问题与排查技巧实录6.1 最常踩的五个错误速查表这几条错误我在带新人时几乎每周都要重复讲干脆整理成一张速查表错误场景报错信息特征解决思路插入数据少了必填字段violates not-null constraint检查字段清单和默认值定义插入的数据撞了唯一键duplicate key value violates unique constraint改用 ON CONFLICT 或先查重插入的外键指向不存在的数据violates foreign key constraint先用 SELECT 验证父表记录存在更新/删除命中了太多行没有报错但影响行数异常养成 RETURNING 或行数检查习惯事务中前面语句报错后继续执行current transaction is aborted立刻 ROLLBACK 而不是继续发 SQL最后一条值得单独多说一句PostgreSQL 的事务一旦遇到错误整个事务就进入 aborted 状态之后你发任何语句都会直接报同样的错唯一出路是 ROLLBACK 后重新开始。很多新手在 psql 里忘了这条规则经常对着一条莫名其妙的报错怀疑人生。记住看见 current transaction is aborted 就代表前面某条语句已经把事务搞挂了。6.2 事务与锁的实战提醒并发环境下增删改最大的隐形杀手是锁。一条 UPDATE 或 DELETE 会锁住它碰到的行另外的事务如果也想改同一行就会一直等待。所以长事务是数据库的大忌。我处理线上问题时会先查pg_stat_activity看看当前有没有长时间未提交的事务卡住锁。给团队的建议很简单在应用层尽量把事务的粒度缩小不要在一个事务里做大量无关操作更不要在事务里等外部接口返回。6.3 索引与自增序列的小细节DELETE 大量数据后表的物理空间不会自动收缩要靠 VACUUM 回收。频繁更新的话PostgreSQL 的 HOT 更新机制能减少索引开销前提是更新的列不包含索引列。如果一张表上有多个索引而你经常改索引列就会产生大量索引版本拖慢查询速度。这些属于进阶优化但在你踩过几次坑之后再回头看会特别有用。另外删除数据不会重置 identity 序列如果测试环境想重置要么 TRUNCATE要么手动setval。最后分享一个我自己的实操习惯凡是影响行数可能超过几百的 UPDATE 或 DELETE我都会先把条件语句存成 SQL 文件执行前用事务包裹执行后看一眼RETURNING或者用GET DIAGNOSTICS拿到行数。宁可慢五分钟也不要把环境搞坏。这套 PostgreSQL 16 的增删改语法看似简单但真正拉开新老手差距的恰恰是这些不起眼的执行纪律。