什么是 UPSERT（INSERT ON CONFLICT）？

Question

Accepted Answer

**UPSERT** 的意思是"插入，如果已存在则更新"。PostgreSQL 通过 **`INSERT ... ON CONFLICT`** 来实现这一功能——插入一行，但如果它会违反唯一约束，则改为更新已有的行（或者什么都不做）。它以原子方式处理了常见的"插入或更新"需求。

## UPSERT 解决的问题

```text
You want to insert a row, but it might already exist (by a unique key):
  ❌ a plain INSERT fails with a unique-violation error if it exists
  ❌ check-then-insert (SELECT, then INSERT or UPDATE) has a RACE CONDITION
     (another transaction could insert between your check and insert)
→ ON CONFLICT does it atomically in ONE statement (no race condition)
```

## ON CONFLICT DO UPDATE —— 插入或更新

```sql
INSERT INTO users (email, name, login_count)
VALUES ('ann@x.com', 'Ann', 1)
ON CONFLICT (email)                  -- if a row with this email already exists...
DO UPDATE SET                        -- ...UPDATE it instead
  name = EXCLUDED.name,              -- EXCLUDED = the values you tried to insert
  login_count = users.login_count + 1;  -- can reference the existing row too
```

如果插入会在 `email` 唯一约束上发生冲突，Postgres 会改为更新已有的行。**`EXCLUDED`** 指代你尝试插入的那些值，同时你也可以引用已有的行（`users.login_count`）——这就支持了诸如递增计数器之类的逻辑。

## ON CONFLICT DO NOTHING —— 插入或跳过

```sql
INSERT INTO users (email, name) VALUES ('ann@x.com', 'Ann')
ON CONFLICT (email) DO NOTHING;      -- if it exists, just skip (no error)
```

`DO NOTHING` 会静默地跳过发生冲突的行——适用于"不存在才插入"且不希望报错的场景。

## 一个常见的实际用途：计数器和幂等插入

```sql
-- track page views — insert on first view, increment on subsequent
INSERT INTO page_views (page_id, views) VALUES (5, 1)
ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + 1;
```

## 为什么这很重要

UPSERT（`INSERT ... ON CONFLICT`）是 PostgreSQL 中一项重要且极具实用价值的功能，它简洁而**安全**地解决了非常常见的"插入或更新"需求，因此理解它对于实际的数据操作很有帮助。

这种需求——插入一行但它可能已经（按某个唯一键）存在——会不断出现（upsert 记录、计数器、幂等插入、数据同步），而朴素的替代方案都有问题：普通 INSERT 在行已存在时会报错失败，而先检查后插入的方式（先 SELECT 再 INSERT 或 UPDATE）存在**竞态条件**（另一个并发事务可能在你检查和插入之间插入数据，导致报错或更新丢失）。`ON CONFLICT` 以**单条语句原子地**解决了这个问题——消除了竞态条件，这正是它对并发应用而言关键的正确性优势。

理解这两种形式——**`DO UPDATE`**（插入或更新，用 `EXCLUDED` 引用尝试插入的值，并能引用已有行来实现诸如递增计数器之类的逻辑）和 **`DO NOTHING`**（插入或静默跳过）——就覆盖了常见的模式（upsert 记录、幂等插入、维护计数器/聚合值）。

由于插入或更新是一项频繁的操作，而正确且安全地（原子地、无竞态条件地）完成它对于并发应用中的数据完整性很重要，因此理解 UPSERT——它解决了什么问题、原子性/避免竞态条件的好处，以及 DO UPDATE/DO NOTHING 两种形式——是一项有价值、可频繁应用的 PostgreSQL 知识，它为一个原本容易出错的常见需求提供了简洁、安全的解决方案，使其成为真实应用中一项实用而重要的功能。