怎样在PostgreSQL中用SQL的ON CONFLICT实现高效插入？-SQL-PHP中文网

ON CONFLICT 高效的关键是：必须有对应唯一约束，冲突检测走索引，避免锁升级，控制事务粒度；推荐用约束名写法（如 ON CONFLICT ON CONSTRAINT users_email_key），批量插入一次提交，DO UPDATE 中避免子查询。

怎样在postgresql中用sql的on conflict实现高效插入？

直接上结论：ON CONFLICT 要想高效，关键不是写对语法，而是让冲突检测走索引、避免锁升级、控制事务粒度，并且必须有对应唯一约束——没约束的列上写 ON CONFLICT (col) 会报错 there is no unique or exclusion constraint matching the ON CONFLICT specification。

必须先建唯一约束，否则ON CONFLICT直接报错

PostgreSQL 不允许你在任意列上用 ON CONFLICT。它只响应明确的唯一性约束（UNIQUE 或 PRIMARY KEY），不是“逻辑上唯一”就行。

如果你只想按 email 去重，就得给 email 加 UNIQUE 约束，不能只靠应用层保证不重复
加约束后，PostgreSQL 才能基于底层索引快速定位冲突行；没索引，冲突检测会退化为全表扫描，性能断崖下跌
用 \d table_name 查看当前约束名，比如 users_email_key，后续推荐用约束名写法，更稳定

用约束名比列名更安全，尤其多唯一索引时

当一张表有多个唯一约束（比如 id 主键 + email 唯一 + phone 唯一），仅写 ON CONFLICT (email) 可能被误推到其他约束上，导致行为不符合预期。

写法推荐：ON CONFLICT ON CONSTRAINT users_email_key —— 明确绑定到 email 的唯一约束
列名写法在单约束表里没问题，但一旦后期加了组合唯一索引（如 (tenant_id, email)），就可能触发歧义甚至报错
约束名可查 information_schema.table_constraints 或 psql 的 \d 命令，别靠猜

批量插入时，VALUES 多行 + ON CONFLICT 一次生效

不要把一批数据拆成 N 条单行 INSERT ... ON CONFLICT，那样每条都走一次解析、计划、锁获取，吞吐量极低。

正确姿势：INSERT INTO users (id, name, email) VALUES (1,'a','a@x'),(2,'b','b@x'),(3,'c','c@x') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;
所有行共享同一个冲突判断逻辑，底层一次索引查找就能处理整批冲突，性能提升常达 5–10 倍
注意：如果某一行冲突目标与其他行不同（比如混用 id 和 email 冲突），必须拆成多个语句，不能强行塞进一条

DO UPDATE 中慎用子查询，容易拖慢并阻塞并发

EXCLUDED 是轻量级虚拟表，但一旦在 SET 里嵌套 SELECT 或函数调用，就可能引入额外锁或执行计划劣化。

避免：DO UPDATE SET updated_at = NOW(), version = (SELECT MAX(version)+1 FROM users u2 WHERE u2.id = EXCLUDED.id) —— 这个子查询可能锁住整张表或引发序列化失败
推荐：DO UPDATE SET updated_at = EXCLUDED.updated_at, version = users.version + 1，纯列运算，无额外 IO
真要依赖外部状态，优先考虑在应用层计算好再传入，而不是塞进 SQL 里实时查

最常被忽略的一点：ON CONFLICT 不是万能去重开关。它只解决“唯一约束冲突”这一种路径，而不会帮你过滤脏数据、校验业务规则或处理部分字段更新的幂等逻辑。这些得靠前置约束、CHECK 规则或应用层兜底，不能指望一条 SQL 全包圆。