Postgres 中的 INSERT ... ON CONFLICT 与 MERGE：该用哪种 upsert，以及各自会抛出的错误

在 PostgreSQL 17 中何时用 INSERT ... ON CONFLICT 做 upsert、何时用 MERGE；为什么 MERGE 在并发下会报重复键错误；以及如何修复 'cannot affect row a second time'、RETURNING 为空和 'no unique or exclusion constraint matching the ON CONFLICT specification'。每条语句都在 Postgres 17.11 上复现过。

Zhan Lu发布于 2026年11月4日阅读约 11 分钟

本页目录

在 Postgres 里，“插入这一行，如果已经存在就更新它”有两种写法：自 9.5 起就有的 INSERT ... ON CONFLICT，以及在 15 中加入、在 17 中借助 RETURNING、merge_action() 和 WHEN NOT MATCHED BY SOURCE 变得实用得多的 MERGE。它们看起来可以互换，其实不然。两个会话同时 upsert 同一个新键时，用 ON CONFLICT 两边都会成功；用 MERGE，其中一个会收到重复键错误。本文复现这个竞态，以及人们在 upsert 时常遇到的另外五种错误和意外，给出每一种的修复方法，最后给出一条选择规则。下文的每条语句和每段输出都来自本地的 PostgreSQL 17.11，也就是 Relyt One 运行的大版本。

简短的答案

当并发写入者可能插入同一个键时，用 INSERT ... ON CONFLICT：请求处理程序、webhook 消费者、计数器都属于这种情况。Postgres 文档保证“an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency”（原子的 INSERT 或 UPDATE 结果；只要没有独立的错误，即使在高并发下也保证得到这两种结果之一）。当你要把一整批行与一张表对账时，比如每晚的数据源或一张暂存表，并且需要条件更新、删除，或者想知道每一行发生了什么，就用 MERGE。

你遇到的	语句	原因	修复
`duplicate key value violates unique constraint`	`MERGE`	两个会话插入同一个新键；`MERGE` 不会回退为 `UPDATE`	这条路径改用 `ON CONFLICT`，或在 SQLSTATE `23505` 时重试
`ON CONFLICT DO UPDATE command cannot affect row a second time`	`INSERT ... ON CONFLICT`	一批数据中同一个键出现了两次	用 `DISTINCT ON` 对这批数据去重
`MERGE command cannot affect row a second time`	`MERGE`	两个源行匹配到同一个目标行	对源数据去重
`RETURNING` 返回为空	两者	只返回实际插入或更新的行	在同一条语句中读取已有的行
`there is no unique or exclusion constraint matching the ON CONFLICT specification`	`INSERT ... ON CONFLICT`	冲突目标没有对应的唯一索引	添加索引，或重复写出部分索引的 `WHERE`
没有变化的行也被重写	两者	即使值相同，更新也会写入一个新的行版本	加上 `WHERE ... IS DISTINCT FROM`
id 出现空洞	`INSERT ... ON CONFLICT`	id 在检测到冲突之前就已取出	接受空洞；不要把 id 当作计数

准备环境

任何 Postgres 17 都可以。使用 Docker：

docker run -d --name upsert-lab -e POSTGRES_PASSWORD=secret postgres:17.11-alpine
docker exec -it upsert-lab psql -U postgres

一张表，以标识列为主键，sku 唯一：

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   text NOT NULL UNIQUE,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);

基本的 upsert 写明冲突列，EXCLUDED 就是你尝试插入的那一行：

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 49.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING id, sku, price, (xmax = 0) AS inserted;

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 49.00 | t

用 45.00 再运行一次，返回的是同一行，已被更新，inserted 为 false：

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 45.00 | f

(xmax = 0) 这个判断是一个被广泛复制的技巧，用来区分插入和更新。它之所以有效，是因为刚插入的行版本没有 xmax，但它依赖的是 Postgres 存储行版本的方式，而不是文档中规定的行为。MERGE 有一种文档化的方式来报告执行的动作，见本文末尾。

1. 两个会话，一个新键：`MERGE` 失败，`ON CONFLICT` 不会

打开两个 psql 会话。在第一个会话中开启事务，用 MERGE upsert 一个尚不存在的 SKU，但不要提交：

-- 会话 A
BEGIN;
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 59.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

在第二个会话中，对同一个 SKU 运行同样的 MERGE。它会挂起：

-- 会话 B
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 55.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

在会话 A 中输入 COMMIT;。会话 B 醒来，报出：

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(WC-01) already exists.

两条时间线。使用 MERGE 时，会话 B 找不到行，尝试插入，等待会话 A，在 A 提交时因重复键错误而失败。使用 INSERT ... ON CONFLICT 时，会话 B 同样等待，然后更新 A 插入的那一行。

过程是这样的。会话 B 开始时，A 的行尚未提交，所以 B 的连接（join）找不到匹配，B 选择了 WHEN NOT MATCHED 分支。随后它的插入在唯一索引上等待 A 结束。A 提交后，这个键已经存在，而 MERGE 此时没有办法切换到 WHEN MATCHED 分支。MERGE 文档也是这么说的：它建议你改用 INSERT ... ON CONFLICT，“as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs”（作为一种替代语句，它能在发生并发 INSERT 时执行 UPDATE）。

现在用 ON CONFLICT 和另一个新 SKU 重现同样的竞态：

-- 会话 A
BEGIN;
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 79.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

-- 会话 B：等待 A
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 75.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

在会话 A 中执行 COMMIT; 之后，会话 B 输出 INSERT 0 1，这一行保存的是 B 的价格：

SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku;

  sku  | price
-------+-------
 HS-01 | 75.00
 WC-01 | 59.00

WC-01 保留了会话 A 的价格，因为会话 B 的 MERGE 失败了。如果你在两个写入者可能创建同一个键的路径上使用 MERGE，就要捕获 SQLSTATE 23505 并重试整个事务。在默认的 READ COMMITTED 隔离级别下，重试会取一个新的快照，看到 A 的行并执行 WHEN MATCHED 分支。在显式事务中只重试这条语句是不行的：错误已经让事务中止，除非你用保存点包住 MERGE 并回滚到该保存点。或者，让这条路径继续使用 ON CONFLICT。

2. “cannot affect row a second time”

一个包含两次同一键的批量 upsert 会整体失败：

INSERT INTO products (sku, name, price)
VALUES ('MN-27', 'Monitor', 199.00),
       ('MN-27', 'Monitor 27"', 189.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

这是有意为之。Postgres 不会去猜两行中哪一行应该胜出，所以拒绝同时应用两者。这种情况常见于由事件流或 CSV 文件构建的批次，同一个键会出现不止一次。在插入之前去重，并决定哪一行胜出。这里让批次中靠后的那一行胜出：

INSERT INTO products (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM (VALUES ('MN-27', 'Monitor', 199.00, 1),
             ('MN-27', 'Monitor 27"', 189.00, 2)) AS batch(sku, name, price, seq)
ORDER BY sku, seq DESC
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING sku, name, price;

  sku  |    name     | price
-------+-------------+--------
 MN-27 | Monitor 27" | 189.00

MERGE 有同样的规则，只是报错信息不同。两个源行匹配到同一个已有的目标行：

MERGE INTO products AS p
USING (VALUES ('KB-01', 'Keyboard', 44.00),
              ('KB-01', 'Keyboard', 43.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

如果这个重复的键在表中还不存在，MERGE 会两次走 WHEN NOT MATCHED 分支，第二次插入则会因重复键错误而失败。两种情况的修复方法相同：每个键只保留一个源行。

3. `RETURNING` 返回为空

DO NOTHING 是表达“不存在就创建”的自然写法，RETURNING id 是拿到 id 的自然写法。但对于已经存在的行，你什么也拿不到：

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO NOTHING
RETURNING id;

 id
----
(0 rows)

RETURNING 只返回实际插入或更新的行。要始终拿到 id，就在同一条语句中读取已有的行：

WITH ins AS (
  INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
  ON CONFLICT (sku) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM products WHERE sku = 'KB-01'
LIMIT 1;

 id
----
  1

还剩一个缺口。如果另一个会话插入了同一个 SKU 且尚未提交，INSERT 会等待它，然后什么也不做；但 SELECT 部分使用的是语句开始时取的快照，所以也看不到这个新行，你会得到零行。把空结果当作“再试一次”。另一种写法 DO UPDATE SET sku = EXCLUDED.sku 总能返回这一行，但每次都会写入一个新的行版本，这正是第 5 节的问题。

4. “there is no unique or exclusion constraint matching the ON CONFLICT specification”

ON CONFLICT (column) 的意思不是“检查这一列是否有重复”，而是“找到恰好建在这些列上的唯一索引并使用它”。写一个没有唯一索引的列，语句在做任何事之前就会失败：

INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00)
ON CONFLICT (name) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

不那么明显的情况是部分唯一索引，这是让邮箱在未被软删除的账户之间保持唯一的常见做法：

CREATE TABLE accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX accounts_live_email ON accounts (email) WHERE deleted_at IS NULL;

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

索引是存在的，但它只覆盖 deleted_at IS NULL 的行，所以除非你在冲突目标中写出同样的条件，否则 Postgres 不会使用它：

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

INSERT 0 1

如果 ON CONFLICT 子句是由你的 ORM 生成的，检查它能否生成这个 WHERE。不要为了迁就 ORM 而把部分索引换成普通唯一索引：那会改变规则，被软删除的账户会一直占用它的邮箱。

5. 重写没有变化的行的 upsert

只要发生冲突，DO UPDATE 就会更新这一行，即使每个值都相同。在 Postgres 中，一次更新会写入一个新的行版本，旧版本成为一个需要 vacuum 清理的死元组。你可以从 xmin 看出来，它是写入当前版本的事务的 id（你的数字会不同）：

SELECT xmin, price FROM products WHERE sku = 'KB-01';
INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;
SELECT xmin, price FROM products WHERE sku = 'KB-01';

 xmin | price
------+-------
  742 | 45.00

 xmin | price
------+-------
  753 | 45.00

价格相同，行版本却是新的。对于一个每小时重新发送一百万行未变数据的同步任务，这就是每小时一百万个死元组，外加 WAL 以及所有在更新时触发的触发器。跳过不改变任何内容的更新：

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
WHERE (products.name, products.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price)
RETURNING id;

 id
----
(0 rows)

xmin 停留在 753。要用 IS DISTINCT FROM 而不是 <>，这样从 NULL 变成别的值或变成 NULL 都算作变化。注意 RETURNING 为空：被 WHERE 过滤掉的行会被加锁但不会被更新，所以不会被返回，这又回到了第 3 节。

6. id 出现空洞

经过上面的所有操作，表中有四个产品：

SELECT id, sku FROM products ORDER BY id;

 id |  sku
----+-------
  1 | KB-01
  3 | WC-01
  5 | HS-01
  9 | MN-27

INSERT ... ON CONFLICT 在检查冲突之前，就先构造出它要插入的行，其中包括下一个标识值。每一次最终变成更新的 upsert、每一次什么也没做的 DO NOTHING、每一次失败的插入尝试，都用掉了一个 id。在尝试插入之前就失败的语句，比如第 4 节中 ON CONFLICT (name) 的错误，则没有用掉。序列文档专门指出了这种情况。走到 WHEN MATCHED 的 MERGE 不会取 id，但失败的 MERGE 插入会：id 4 就被第 1 节中的会话 B 用掉了。

只要没有地方把 id 当作计数，这就无害。如果一条高流量的 upsert 路径消耗 32 位 integer id 的速度超出了你的预期，就用 bigint，像上面的表那样。

`MERGE` 能做而 `ON CONFLICT` 做不到的事

到目前为止，MERGE 看起来都是风险更大的选择。但对另一类工作，它是更好的选择：把一张表与一个完整的数据源对账。比如供应商每晚发来一份完整的价目表，你把它加载到一张暂存表中：

CREATE TABLE price_feed (
  sku   text PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO price_feed VALUES
  ('KB-01', 'Keyboard',    42.00),
  ('MN-27', 'Monitor 27"', 189.00),
  ('HS-01', 'Headset',     79.00),
  ('MS-01', 'Mouse',       19.00);

一条 MERGE 就能更新有变化的行、插入新行、删除供应商去掉的行，并告诉你每一行发生的是这三种中的哪一种：

MERGE INTO products AS p
USING price_feed AS f
ON p.sku = f.sku
WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (f.name, f.price) THEN
  UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), p.sku, p.price;

 merge_action |  sku  | price
--------------+-------+-------
 UPDATE       | KB-01 | 42.00
 UPDATE       | HS-01 | 79.00
 INSERT       | MS-01 | 19.00
 DELETE       | WC-01 | 59.00

输出中没有 MN-27，因为它没有变化。这条语句中有三部分是 Postgres 17 新增的：MERGE 上的 RETURNING、merge_action() 函数，以及 WHEN NOT MATCHED BY SOURCE。在 Postgres 16 上，MERGE ... RETURNING 是语法错误。在 17 之前，同样的同步需要一次 upsert、一条单独的 DELETE ... WHERE NOT EXISTS，再加上一些记录工作来报告变化了什么。

WHEN NOT MATCHED BY SOURCE THEN DELETE 会删除表中所有不在数据源里的行。如果把它指向一份不完整的数据源，或者加载失败后的一张空暂存表，它就会删除你本想保留的行。运行前先检查数据源的行数，或者在目标表的列上加一个条件，例如 WHEN NOT MATCHED BY SOURCE AND p.supplier = 'acme' THEN DELETE。

第 1 节中关于并发的注意事项仍然适用。每晚的同步通常是这些键唯一的写入者，这也是 MERGE 适合它的原因。如果在同步运行期间，请求流量也可能插入相同的 SKU，就在遇到 23505 时重试同步。

该用哪一个

写入者可能同时插入同一个新键：INSERT ... ON CONFLICT DO UPDATE。
用一个完整的数据源对账一张表，一次完成更新、插入和删除：MERGE；如果还有其他写入者写这些键，在 SQLSTATE 23505 时重试。
需要逐行知道它是被插入、更新还是删除：在 Postgres 17 上用 MERGE ... RETURNING merge_action()。
批量数据：无论用哪种语句，都先按键去重。
获取或创建：DO NOTHING 加上读取已有的行，并把零行当作“重试”。
重新发送未变的数据：在更新上加 WHERE ... IS DISTINCT FROM。
部分唯一索引：在冲突目标中重复写出索引的 WHERE。

在数据副本上演练同步

本文中有风险的语句是带 DELETE 的那一条。在把一条新的 MERGE 用于生产环境之前，先在生产数据的副本上运行它。在 Relyt One 上，每个分支都在共享的写时复制存储之上拥有自己的计算实例，所以你可以为数据库创建一个分支，把今晚的数据源加载到那里的暂存表中，运行 MERGE，并查看 merge_action() 的输出。如果它打算删掉半个商品目录，你会在分支上发现，然后把这个分支丢掉。Relyt One 运行的是 PostgreSQL 17，所以 RETURNING、merge_action() 和 WHEN NOT MATCHED BY SOURCE 在那里的行为与上文所示一致。

免费开始使用

标签Postgres sqlupsertmerge

Postgres 中的 INSERT ... ON CONFLICT 与 MERGE：该用哪种 upsert，以及各自会抛出的错误

简短的答案

准备环境

1. 两个会话，一个新键：`MERGE` 失败，`ON CONFLICT` 不会

2. “cannot affect row a second time”

3. `RETURNING` 返回为空

4. “there is no unique or exclusion constraint matching the ON CONFLICT specification”

5. 重写没有变化的行的 upsert

6. id 出现空洞

`MERGE` 能做而 `ON CONFLICT` 做不到的事

该用哪一个

在数据副本上演练同步

Postgres 的 ID 为什么会跳号，以及如何修复导入数据后的「duplicate key」错误

用 Postgres 做任务队列：把 FOR UPDATE SKIP LOCKED 用对

PgBouncer 事务池模式下会出哪些问题，以及如何逐一修复

下一个数据库，只差一个分支。

简短的答案

准备环境

1. 两个会话，一个新键：MERGE 失败，ON CONFLICT 不会

2. “cannot affect row a second time”

3. RETURNING 返回为空

4. “there is no unique or exclusion constraint matching the ON CONFLICT specification”

5. 重写没有变化的行的 upsert

6. id 出现空洞

MERGE 能做而 ON CONFLICT 做不到的事

该用哪一个

在数据副本上演练同步

相关文章

Postgres 的 ID 为什么会跳号，以及如何修复导入数据后的「duplicate key」错误

用 Postgres 做任务队列：把 FOR UPDATE SKIP LOCKED 用对

PgBouncer 事务池模式下会出哪些问题，以及如何逐一修复

下一个数据库，只差一个分支。

1. 两个会话，一个新键：`MERGE` 失败，`ON CONFLICT` 不会

3. `RETURNING` 返回为空

`MERGE` 能做而 `ON CONFLICT` 做不到的事