20. DELETE 与 UPSERT（ON CONFLICT）

这一章讲两件和数据改动有关的事：删除（DELETE）和「存在则更新、不存在则插入」（UPSERT）。

一、DELETE 删除数据

DELETE 用来删除表里的一行或多行，只动数据，不动表结构。

基本语法：

DELETE FROM 表名
WHERE 条件;

先准备点数据：

INSERT INTO users (username, email, age)
VALUES
  ('xiaoming', 'ming@example.com', 28),
  ('xiaohong', 'hong@example.com', 22),
  ('wangwu',   'wang@example.com', 35);

删除符合条件的行

删掉 wangwu 这一行：

DELETE FROM users
WHERE username = 'wangwu';

回显 DELETE 1，表示删了 1 行。

用 RETURNING 取回被删的行

和 INSERT、UPDATE 一样，DELETE 也能用 RETURNING 把删掉的数据返回来，常用于「删之前先备份一份」。

DELETE FROM users
WHERE username = 'xiaohong'
RETURNING *;

一次删多行

条件可以匹配多行，比如删掉所有年龄小于 25 的用户：

DELETE FROM users
WHERE age < 25
RETURNING username;

用 USING 跨表删除

DELETE 也能借助 USING 关联另一张表来定位要删的行。比如删掉「年龄大于 60 岁的用户」名下的所有订单：

DELETE FROM orders
USING users
WHERE orders.user_id = users.id
  AND users.age > 60;

这里先按 user_id = id 把两表连起来，只删掉匹配上的订单。注意：DELETE 的是 orders，USING 只是提供判断依据，并不会删 users。

删除返回 0 行不是报错

如果 WHERE 没匹配到任何行，会回 DELETE 0。这是正常结果，不是出错。先确认条件是否写对、数据是否真的存在。

二、UPSERT：存在就更新，不存在就插入

UPSERT 是 update（更新）和 insert（插入）的组合词。场景很常见：拿到一批数据，数据库里有了就更新它，没有就新增。

PostgreSQL 没有单独的 UPSERT 关键字，而是用 INSERT ... ON CONFLICT 实现。

基本语法：

INSERT INTO 表名 (列...)
VALUES (值...)
ON CONFLICT (冲突列)
DO NOTHING | DO UPDATE SET 列 = 值;

要点：

• ON CONFLICT (冲突列) 里的冲突列，必须是主键或带**唯一约束（UNIQUE）**的列。PostgreSQL 靠它判断是否「已存在」。
• DO NOTHING：冲突了就什么都不做，不报错。
• DO UPDATE SET ...：冲突了就改成新值。

准备一个唯一约束

users 的 username 目前没有唯一约束，先加上，才能拿它当冲突列：

ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username);

例子 1：冲突就跳过（DO NOTHING）

INSERT INTO users (username, email, age)
VALUES ('xiaoming', 'ming@example.com', 28)
ON CONFLICT (username)
DO NOTHING;

如果 xiaoming 已存在，这条语句什么也不做，也不会报错，回显 INSERT 0 0。

例子 2：冲突就更新（DO UPDATE）

INSERT INTO users (username, email, age)
VALUES ('xiaoming', 'ming_new@example.com', 29)
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  age   = EXCLUDED.age;

EXCLUDED 是个特殊关键字，代表「这次本想插入的那一行」。所以 EXCLUDED.email 就是你想插入的新邮箱。冲突时，旧行被更新成新值。

冲突列可以是多列

如果唯一约束建在多个列上，ON CONFLICT 也要列出同样的列。比如给 orders 加一个「同一用户对同一状态只能有一条」的约束：

ALTER TABLE orders ADD CONSTRAINT orders_user_status_key UNIQUE (user_id, status);

然后按这两列做 UPSERT：

INSERT INTO orders (user_id, amount, status)
VALUES (1, 50.00, 'pending')
ON CONFLICT (user_id, status)
DO UPDATE SET amount = EXCLUDED.amount, created_at = now();

按约束名指定冲突（ON CONSTRAINT）

冲突目标也能写成约束名，而不是列清单，效果一样：

INSERT INTO orders (user_id, amount, status)
VALUES (1, 50.00, 'pending')
ON CONFLICT ON CONSTRAINT orders_user_status_key
DO NOTHING;

当唯一约束涉及多列、或你更想按名字引用时，用 ON CONSTRAINT 更清楚。

UPSERT 也能 RETURNING

和 INSERT 一样，UPSERT 末尾可以加 RETURNING，不管最终是插入还是更新，都能拿回那一行：

INSERT INTO users (username, email, age)
VALUES ('xiaoming', 'ming@example.com', 28)
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id, username, email;

DO UPDATE 里加 WHERE 条件

只有满足额外条件时才更新，比如「金额变了才改」：

INSERT INTO orders (user_id, amount, status)
VALUES (1, 50.00, 'pending')
ON CONFLICT (user_id, status)
DO UPDATE SET amount = EXCLUDED.amount
WHERE orders.amount <> EXCLUDED.amount;

当新旧金额一样时，这条更新会被跳过，避免无意义的写入。

常见错误

• 冲突列没有唯一约束：会报「没有唯一的约束匹配」的错误。先用 UNIQUE 或主键把冲突列锁住。
• 把 EXCLUDED 用在 DO NOTHING 里：EXCLUDED 只在 DO UPDATE 分支有意义。
• 漏写 WHERE 的 DELETE：清空整张表，务必确认。

清空整张表：TRUNCATE

如果确定要删光所有行，用 TRUNCATE 比 DELETE 不带 WHERE 更快：

TRUNCATE TABLE users;

它不逐行记日志，直接释放整个表的数据。还有两个常用选项：

• RESTART IDENTITY：把自增列（identity）的计数也重置，下次插入从 1 开始。
• CASCADE：连同引用了本表的外键表一起清空，要谨慎。

TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

对比项	DELETE（不带 WHERE）	TRUNCATE
速度	逐行删，较慢	整表释放，很快
能否用 WHERE	能	不能
能否 RETURNING	能	不能
重置自增	否	加 RESTART IDENTITY 可重置

DELETE 与 USING 的注意点

前面讲过用 USING 跨表删除。再提醒一句：DELETE 的是 FROM 后面那张表，USING 只是提供判断依据，不会动另一张表。写错了表名，可能误删的是你没想到的那张。

一个完整的 UPSERT 实战

假设你拿到一份最新用户名单，要「已存在的更新邮箱，不存在的新增」。username 已有唯一约束，直接逐个 UPSERT 即可：

INSERT INTO users (username, email, age)
VALUES ('xiaoming', 'ming_new@example.com', 29)
ON CONFLICT (username)
DO UPDATE SET
  email = EXCLUDED.email,
  age   = EXCLUDED.age
RETURNING id, username, email;

如果一次来很多条，把 VALUES 写成多组，或者配合 INSERT … SELECT 从临时表导入，逻辑完全一样。核心就一句：冲突列有唯一约束，DO NOTHING 跳过、DO UPDATE 改写。

没有唯一约束会怎样

如果忘了给冲突列加唯一约束，PostgreSQL 不知道什么叫「已存在」，会直接报错：

ERROR:  ON CONFLICT 子句没有匹配的唯一约束或排除约束

所以写 UPSERT 前，先确认冲突列上确实有 PRIMARY KEY 或 UNIQUE。这一条卡住过不少新手。

原行和新值怎么同时用

DO UPDATE 里，EXCLUDED 是「想插入的新行」，而被冲突命中的「原行」直接用列名引用即可。比如只在「新金额比原金额高」时才覆盖：

INSERT INTO orders (user_id, amount, status)
VALUES (1, 200.00, 'pending')
ON CONFLICT (user_id, status)
DO UPDATE SET amount = EXCLUDED.amount
WHERE EXCLUDED.amount > orders.amount;

这里 orders.amount 是表里原来的金额，EXCLUDED.amount 是新来的金额，两者能同时出现在条件里。

小结

• DELETE 删行，记得带 WHERE，否则清空整张表；TRUNCATE 是更快的整表清空方式。
• RETURNING 在 DELETE 里同样能取回被删的行。
• UPSERT 用 INSERT ... ON CONFLICT：冲突列须为主键或唯一约束。
• DO NOTHING 跳过冲突，DO UPDATE 用 EXCLUDED 引用新值来更新。
• UPSERT 支持多列冲突、ON CONSTRAINT、自带 WHERE 和 RETURNING。