# 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 上复现过。

**在 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 文档](https://www.postgresql.org/docs/17/sql-insert.html)保证“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：

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

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

```sql
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` 就是你尝试插入的那一行：

```sql
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;
```

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

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

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

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

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

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

```sql
-- 会话 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`。它会挂起：

```sql
-- 会话 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 醒来，报出：

```text
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 插入的那一行。](/blog/assets/59297665cb507597.svg)

过程是这样的。会话 B 开始时，A 的行尚未提交，所以 B 的连接（join）找不到匹配，B 选择了 `WHEN NOT MATCHED` 分支。随后它的插入在唯一索引上等待 A 结束。A 提交后，这个键已经存在，而 `MERGE` 此时没有办法切换到 `WHEN MATCHED` 分支。[`MERGE` 文档](https://www.postgresql.org/docs/17/sql-merge.html)也是这么说的：它建议你改用 `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 重现同样的竞态：

```sql
-- 会话 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;
```

```sql
-- 会话 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 的价格：

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

```text
  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 会整体失败：

```sql
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;
```

```text
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 文件构建的批次，同一个键会出现不止一次。在插入之前去重，并决定哪一行胜出。这里让批次中靠后的那一行胜出：

```sql
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;
```

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

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

```sql
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);
```

```text
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 的自然写法。但对于已经存在的行，你什么也拿不到：

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

```text
 id
----
(0 rows)
```

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

```sql
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;
```

```text
 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)` 的意思不是“检查这一列是否有重复”，而是“找到恰好建在这些列上的唯一索引并使用它”。写一个没有唯一索引的列，语句在做任何事之前就会失败：

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

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

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

```sql
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;
```

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

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

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

```text
INSERT 0 1
```

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

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

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

```sql
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';
```

```text
 xmin | price
------+-------
  742 | 45.00

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

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

```sql
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;
```

```text
 id
----
(0 rows)
```

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

## 6. id 出现空洞

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

```sql
SELECT id, sku FROM products ORDER BY id;
```

```text
 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)` 的错误，则没有用掉。[序列文档](https://www.postgresql.org/docs/17/functions-sequence.html)专门指出了这种情况。走到 `WHEN MATCHED` 的 `MERGE` 不会取 id，但失败的 `MERGE` 插入会：id 4 就被第 1 节中的会话 B 用掉了。

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

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

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

```sql
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` 就能更新有变化的行、插入新行、删除供应商去掉的行，并告诉你每一行发生的是这三种中的哪一种：

```sql
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;
```

```text
 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](https://www.postgresql.org/docs/17/release-17.html) 新增的：`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` 时重试同步。

## 该用哪一个

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

## 在数据副本上演练同步

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

[免费开始使用](/auth/signin?ref=blog-postgres-upsert-on-conflict-vs-merge)