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 1psql 对 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 更安全,因为判断和写入由数据库在单条语句中协调。
按自己的节奏,确认真正理解。