# UPSERT（ON CONFLICT）

> 使用 ON CONFLICT、EXCLUDED 和 RETURNING，以原子方式完成插入或更新。

---

LLMS 索引： [llms.txt](/llms.txt)

---

插入或更新（UPSERT）是指“如果不存在就插入，否则更新”的操作。PostgreSQL 将其写作 `INSERT ... ON CONFLICT ... DO UPDATE`（或 `DO NOTHING`）。它具有原子性、不会产生竞态，也是生产代码中最常用的模式之一。在这个子句出现之前，应用程序必须采用 `SELECT-then-INSERT-then-UPDATE` 的绕行方式，在并发场景下要么很慢，要么结果错误。

## 本章要点 {#learn}

- 使用 `INSERT ... ON CONFLICT DO NOTHING` 跳过重复项
- 使用 `INSERT ... ON CONFLICT (col) DO UPDATE SET ...` 合并新值与旧值
- 通过 `EXCLUDED` 虚拟表引用待插入的值
- 冲突目标：列、约束名称或表达式
- `RETURNING` 与 `ON CONFLICT` 的配合，以及何时能返回该行

## 示例 {#example}

```sql {num="25.1" caption="UPSERT（ON CONFLICT）"}
-- Setup a table with a unique key
DROP TABLE IF EXISTS visitors_example;
CREATE TABLE visitors_example (
  email      text PRIMARY KEY,
  name       text NOT NULL,
  visits     integer NOT NULL DEFAULT 1,
  last_seen  timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice');

-- DO NOTHING: insert if absent, otherwise skip silently
INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice2')
ON CONFLICT (email) DO NOTHING;

-- DO UPDATE: on conflict, merge new values into the existing row
INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
  SET visits    = visitors_example.visits + 1,
      last_seen = current_timestamp;

-- EXCLUDED refers to the row that was *proposed* by the INSERT
INSERT INTO visitors_example (email, name) VALUES ('bob@example.com', 'Bob')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,             -- use the new name
      visits = visitors_example.visits + 1
RETURNING *;

-- Conflict on a partial / expression unique index works too
-- CREATE UNIQUE INDEX ON visitors_example (lower(email));
-- INSERT ... ON CONFLICT ((lower(email))) DO UPDATE ...

SELECT * FROM visitors_example ORDER BY email;
```

冲突目标——`ON CONFLICT` 后的带括号子句——告诉 PostgreSQL 哪个唯一约束或索引定义了冲突。`(email)` 与 `email` 列上的唯一键匹配。你也可以指定约束名称：`ON CONFLICT ON CONSTRAINT visitors_example_pkey DO UPDATE ...`。对于表达式索引，重复写出该表达式：`ON CONFLICT ((lower(email)))`。部分索引还可能要求在冲突目标之后提供匹配的谓词。

`EXCLUDED` 是一个虚拟表，表示 `INSERT` 提出的那一行。`visitors_example`（或者实际表的名称）指的是已有行。把两者结合起来，就能表达递增（`SET visits = visitors_example.visits + 1`）、合并（`SET name = COALESCE(EXCLUDED.name, visitors_example.name)`）等操作。

`RETURNING` 与 `ON CONFLICT DO UPDATE` 搭配时，会返回每一条受影响的行（插入或更新的行）。`DO NOTHING` *只*返回真正插入的行——如果跳过了冲突，就什么也不会返回。

## 运行示例 {#run}

运行方式：

```text
$ psql -f source/upsert.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 0       -- DO NOTHING skipped the duplicate
INSERT 0 1       -- DO UPDATE merged
INSERT 0 1
       email       | name  | visits |           last_seen
-------------------+-------+--------+-------------------------------
 bob@example.com   | Bob   |      1 | 2024-xx-xx xx:xx:xx.xxxxxx+00
(1 row)

       email       | name  | visits |           last_seen
-------------------+-------+--------+-------------------------------
 alice@example.com | Alice |      2 | 2024-xx-xx xx:xx:xx.xxxxxx+00
 bob@example.com   | Bob   |      1 | 2024-xx-xx xx:xx:xx.xxxxxx+00
(2 rows)
```

## 常见陷阱 {#pitfalls}

- `ON CONFLICT` 要求事先定义相匹配的唯一约束或索引。没有这样的约束或索引，PostgreSQL 无法知道哪一行会发生冲突，并会抛出错误。
- 冲突目标必须与某个约束或索引完全匹配。只有在你为 `lower(email)` 建立了唯一的*表达式*索引时，`ON CONFLICT (lower(email))` 才能工作。
- `DO NOTHING` 适合你确实想要“尽力插入”的场景。如果你其实期待插入该行，它会悄无声息地吞掉真正的错误。
- `DO NOTHING` 后的 `RETURNING` 不会为被跳过的插入返回一行。如果你需要“无论是插入了该行，还是该行原本就存在，都返回这行”，请使用 `DO UPDATE`，并设置一个无操作的 `SET`，例如 `SET email = EXCLUDED.email`，这样总会返回一行。

> [!TIP] 提示
> 从 PostgreSQL 15 开始，SQL 标准中的 `MERGE` 语句也可用——它更灵活（可以包含多个匹配和不匹配分支），但也更冗长。对于简单的插入或更新，`INSERT ... ON CONFLICT` 仍然是合适的工具。

> [!NOTE] 动手练习
> 将第二条插入语句（`DO NOTHING`）运行两次——Alice 的访问次数不应增加。然后将第三条（`DO UPDATE`）运行两次——访问次数每次都应增加。给每条语句都加上 `RETURNING *`，观察哪些语句会返回行。

## 下载 {#downloads}

SQL 文件：[upsert.sql](/examples/source/upsert.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)