PostgreSQL / 系统课时

处理唯一键冲突

处理唯一键冲突

本课解决“插入数据时遇到重复主键或唯一键怎么办”的问题。你将使用 INSERT ... ON CONFLICT 实现幂等写入,并掌握 DO NOTHING 与 DO UPDATE 的选择方式。

学习目标

  • 理解 ON CONFLICT 的触发条件
  • 使用 DO NOTHING 忽略重复数据
  • 使用 DO UPDATE 更新已有记录

冲突从哪里产生

当 INSERT 违反主键、唯一约束或可推断的唯一索引时,PostgreSQL 可以进入 ON CONFLICT 分支。它不能处理普通检查约束、非空约束或外键错误。常见结构是:

sql
INSERT INTO 表名 (...)
VALUES (...)
ON CONFLICT (唯一列)
DO NOTHING;

或使用 DO UPDATE,把待插入记录中的值合并到已有行。DO UPDATE 中的 excluded 代表“本次原本准备插入的那一行”。

忽略冲突

下面示例以 email 唯一约束识别重复用户。第二次插入相同 email 时不报错,也不修改原记录。

sql
DROP TABLE IF EXISTS demo_users;

CREATE TABLE demo_users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL
);

INSERT INTO demo_users (email, display_name)
VALUES ('alice@example.com', 'Alice');

INSERT INTO demo_users (email, display_name)
VALUES ('alice@example.com', 'Alice New')
ON CONFLICT (email) DO NOTHING;

SELECT id, email, display_name
FROM demo_users;

预期结果。第二条 INSERT 影响 0 行,表中仍保留原名称 Alice。

text
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 0

 id |       email       | display_name
----+-------------------+--------------
  1 | alice@example.com | Alice
(1 row)

冲突时更新

当业务要求“有则更新、无则插入”时,可使用 DO UPDATE。下面示例更新名称,并通过 RETURNING 返回最终记录。

sql
INSERT INTO demo_users (email, display_name)
VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email)
DO UPDATE SET
    display_name = excluded.display_name
RETURNING id, email, display_name;

预期结果。已有行被更新,id 不变,display_name 变为 Alice Updated。

text
 id |       email       |  display_name
----+-------------------+----------------
  1 | alice@example.com | Alice Updated
(1 row)

INSERT 0 1

psql 对 ON CONFLICT DO UPDATE 仍显示 INSERT 0 1,因为命令类型是 INSERT。

更新条件与边界

DO UPDATE 可以增加 WHERE,只在满足条件时更新。例如库存版本号、更新时间或业务状态符合要求时才覆盖:

sql
INSERT INTO demo_users (email, display_name)
VALUES ('alice@example.com', 'Alice Final')
ON CONFLICT (email)
DO UPDATE SET
    display_name = excluded.display_name
WHERE demo_users.display_name <> excluded.display_name
RETURNING id, display_name;

预期结果。当前名称与新名称不同,因此完成更新并返回 Alice Final。

text
 id | display_name
----+--------------
  1 | Alice Final
(1 row)

INSERT 0 1

冲突目标必须与实际唯一约束或唯一索引匹配。若表中没有对应唯一性规则,仅写 ON CONFLICT (email) 会报错。并发场景下,ON CONFLICT 比先 SELECT 再 INSERT 更安全,因为判断和写入由数据库在单条语句中协调。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

处理唯一键冲突

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通