PostgreSQL增删改核心语法与进阶实战：INSERT/UPDATE/DELETE

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这个关键字，它代表的是"如果不冲突、本来要插入的那行数据"。上面这条语句的意思就是：如果 id=5 不存在就插入，存在就把名字和成绩更新成新值，这就是常说的 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 ... FROM：

UPDATE 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 的区别，这里我给出一张对比表，专门讲清楚它们各自的适用场景：

如果你的需求就是"把这张表全部数据清掉，并且序列号从 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 拿回 id：

INSERT 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 最常踩的五个错误速查表

这几条错误我在带新人时几乎每周都要重复讲，干脆整理成一张速查表：

最后一条值得单独多说一句：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 的增删改语法看似简单，但真正拉开新老手差距的，恰恰是这些不起眼的执行纪律。