25 UPSERT（ON CONFLICT）

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

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

本章要点

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

示例

示例 25.1 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 只返回真正插入的行——如果跳过了冲突，就什么也不会返回。

运行示例

运行方式：

$ 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)

常见陷阱

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

提示

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

动手练习

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

25 UPSERT（ON CONFLICT）

本章要点

示例

运行示例

常见陷阱

下载