跳到主要内容

PostgreSQL UPSERT 教程

UPSERT = INSERT + UPDATE：插入时若与已有行冲突（违反唯一约束/主键），则改为更新或忽略。PostgreSQL 用 INSERT ... ON CONFLICT 实现。

本篇内容

ON CONFLICT DO NOTHING：冲突则忽略
ON CONFLICT DO UPDATE：冲突则更新
冲突目标与 EXCLUDED 伪表
与唯一约束的关系和常见报错

冲突则忽略

-- 邮箱已存在时静默跳过，不报错
INSERT INTO users (name, email) VALUES ('Jack', 'jack@example.com')
ON CONFLICT (email) DO NOTHING;

冲突则更新

-- 邮箱已存在时更新姓名，EXCLUDED 代表「试图插入的那一行」
INSERT INTO users (name, email, login_count) VALUES ('Jack', 'jack@example.com', 1)
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1;

前提条件

ON CONFLICT 必须指定能对应到某个唯一索引/主键的冲突目标，否则 PostgreSQL 无法判断「冲突」：

-- 需要 users(email) 上存在唯一约束或唯一索引
-- 没有唯一约束时写 ON CONFLICT (email) 会直接报错

待补充（TODO）

补充：批量 UPSERT 的写法与「同一语句内多行冲突」的行为
补充：ON CONFLICT ON CONSTRAINT 指定约束名的用法
补充：DO UPDATE 中的 WHERE 条件更新（避免覆盖较新的数据）
补充：UPSERT 的并发行为（并发插入时的锁与重试）

本篇内容
冲突则忽略
冲突则更新
前提条件