PostgreSQL 实战：详解 UPSERT（INSERT ON CONFLICT）

在现代应用开发中，“存在则更新，不存在则插入”是极其常见的数据操作模式，例如：

• 用户首次访问时创建记录，后续访问更新最后登录时间
• 电商商品库存的累加（而非覆盖）
• 实时统计指标（如 PV/UV 计数器）
• 缓存写入（缓存穿透场景）

PostgreSQL 从9.5 版本开始提供了标准 SQL 的INSERT ... ON CONFLICT语法（即UPSERT），彻底解决了这一痛点。本文将从基础用法、高级技巧、性能优化、避坑指南四个维度，带你全面掌握 UPSERT 的精髓。

一、UPSERT 基础

1.1 为什么需要UPSERT？- 传统方案的缺陷

在没有 UPSERT 之前，开发者通常采用两种方式：

1、方案 A：先查后插（Race Condition 风险）

# 伪代码ifnotdb.exists(user_id):db.insert(user_id,...)else:db.update(user_id,...)

• 问题：高并发下可能多次插入（违反唯一约束）
• 后果：程序崩溃或数据不一致

2、方案 B：捕获异常（性能差 + 逻辑复杂）

BEGIN;INSERTINTOusersVALUES(1,'Alice');EXCEPTIONWHENunique_violationTHENUPDATEusersSETname='Alice'WHEREid=1;END;

• 问题：频繁抛异常开销大，代码冗长

✅UPSERT 的价值：
原子性 + 高性能 + 简洁语法，一行 SQL 解决所有问题。

1.2 替代方案对比

结论：坚持使用INSERT ... ON CONFLICT，它是 PostgreSQL 社区验证的最佳实践。

1.3 跨数据库兼容性

若需跨数据库，可封装适配层，或使用Django ORM / SQLAlchemy的方言抽象。

1.4 UPSERT 使用建议

💡终极心法：
“UPSERT 不是魔法，而是精心设计的原子操作。”
正确使用它，你的应用将获得数据一致性、高并发能力和简洁代码三重收益。

二、基本使用

2.1 核心语法：INSERT ... ON CONFLICT

1、基本结构

INSERTINTOtable_name(column1,column2,...)VALUES(value1,value2,...)ONCONFLICT[conflict_target]DOUPDATESETcolumn1=excluded.column1,column2=excluded.column2,...[WHEREcondition];

关键组件解析：

2、最简示例：存在则更新所有字段

假设用户表：

CREATETABLEusers(idSERIALPRIMARYKEY,emailVARCHAR(255)UNIQUENOTNULL,nameVARCHAR(100),last_loginTIMESTAMP);

UPSERT 操作：

INSERTINTOusers(email,name,last_login)VALUES('alice@example.com','Alice',NOW())ONCONFLICT(email)-- 冲突目标：email 唯一索引DOUPDATESETname=excluded.name,last_login=excluded.last_login;

✅ 效果：

• 若email不存在 → 插入新行
• 若email已存在 → 更新name和last_login

💡excluded.name表示“本次 INSERT 语句中提供的 name 值”

2.2 突目标（Conflict Target）详解

1、指定列（最常用）

ONCONFLICT(email)-- 基于 email 列的唯一约束

2、指定约束名（更精确）

-- 先创建命名约束ALTERTABLEusersADDCONSTRAINTuk_users_emailUNIQUE(email);-- 使用约束名ONCONFLICTONCONSTRAINTuk_users_email

3、部分索引（Partial Index）冲突

-- 创建部分唯一索引：仅对 active=true 的记录生效CREATEUNIQUEINDEXidx_active_emailONusers(email)WHEREactive=true;-- UPSERT 时指定该索引INSERTINTOusers(email,name,active)VALUES('bob@example.com','Bob',true)ONCONFLICT(email)WHEREactive=true-- 必须匹配部分索引条件DOUPDATESETname=excluded.name;

注意：WHERE active = true必须与索引定义一致，否则无法触发冲突检测！

2.3 返回结果：RETURNING子句

UPSERT 支持RETURNING，可获取实际插入或更新的行：

INSERTINTOusers(email,name)VALUES('alice@example.com','Alice')ONCONFLICT(email)DOUPDATESETname=excluded.nameRETURNINGid,email,name,'inserted'ASaction;-- 但无法区分是插入还是更新！

如何区分插入 vs 更新？

方法 1：使用 CTE + 标记

WITHupsertAS(INSERTINTOusers(email,name)VALUES('alice@example.com','Alice')ONCONFLICT(email)DOUPDATESETname=excluded.nameRETURNING*,'updated'ASaction),insertedAS(INSERTINTOusers(email,name)SELECT'alice@example.com','Alice'WHERENOTEXISTS(SELECT1FROMusersWHEREemail='alice@example.com')RETURNING*,'inserted'ASaction)SELECT*FROMupsertUNIONALLSELECT*FROMinserted;

复杂且有竞态风险，不推荐

方法 2：应用层判断（推荐）

• 执行 UPSERT 前先查是否存在
• 或通过业务逻辑推断（如首次注册 vs 登录）

现实建议：大多数场景无需区分，直接使用RETURNING获取最新数据即可。

三、高级技巧：精细化控制更新逻辑

3.1 条件更新（避免无意义写入）

场景：只在新登录时间 > 旧时间时才更新

INSERTINTOusers(email,last_login)VALUES('alice@example.com','2026-01-25 10:00:00')ONCONFLICT(email)DOUPDATESETlast_login=excluded.last_loginWHEREusers.last_login<excluded.last_login;-- 仅当新时间更新时才更新

3、优势：

• 减少 WAL 日志
• 避免触发不必要的触发器
• 提升性能（尤其高频更新场景）

3.2 部分字段更新（保留原值）

场景：只更新last_login，不修改name

INSERTINTOusers(email,name,last_login)VALUES('alice@example.com','OldName',NOW())-- name 值会被忽略ONCONFLICT(email)DOUPDATESETlast_login=excluded.last_login;-- 不更新 name

💡 即使 INSERT 中提供了name，只要DO UPDATE SET不包含它，就不会被修改。

3.3 累加操作（计数器场景）

场景：用户访问次数 +1

INSERTINTOuser_visits(user_id,visit_count)VALUES(123,1)ONCONFLICT(user_id)DOUPDATESETvisit_count=user_visits.visit_count+1;-- 累加而非覆盖

安全替代：

-- 更健壮：防止初始值为 NULLDOUPDATESETvisit_count=COALESCE(user_visits.visit_count,0)+1;

3.4 DO NOTHING：静默忽略冲突

场景：只插入新记录，冲突时不做任何操作

INSERTINTOlogs(event_id,data)VALUES('evt_001','{"action":"click"}')ONCONFLICT(event_id)DONOTHING;-- 冲突时直接跳过

返回：受影响行数为 0（可通过RETURNING *验证是否插入）

3.5 性能优化：索引与执行计划

1、必建索引

UPSERT 的性能完全依赖冲突目标上的索引：

-- 对 ON CONFLICT (email) 必须有唯一索引CREATEUNIQUEINDEXCONCURRENTLY idx_users_emailONusers(email);

使用CONCURRENTLY避免锁表（生产环境必备）

2、执行计划分析

EXPLAIN(ANALYZE,BUFFERS)INSERTINTOusers(email,name)VALUES('test@example.com','Test')ONCONFLICT(email)DOUPDATESETname=excluded.name;

关键观察点：

• Index Only Scan：理想情况（仅扫描索引）
• Heap Fetches：越少越好（表示需回表）
• Buffers：shared hit高表示缓存命中率高

3、批量 UPSERT（高性能写入）

单条 UPSERT 有网络开销，批量操作更高效：

-- 方式 1：多值插入INSERTINTOusers(email,name)VALUES('a@example.com','A'),('b@example.com','B'),('c@example.com','C')ONCONFLICT(email)DOUPDATESETname=excluded.name;-- 方式 2：从临时表导入CREATETEMPTABLEtemp_users(emailTEXT,nameTEXT);-- ... 填充临时表INSERTINTOusersSELECT*FROMtemp_usersONCONFLICT(email)DOUPDATESETname=excluded.name;

性能对比（10万条）：

四、常见陷阱与避坑指南

陷阱 1：冲突目标未命中索引

-- 表有唯一索引 (email, status)-- 但 UPSERT 只指定 (email)ONCONFLICT(email)-- ❌ 无法触发冲突！

✅ 解决：冲突目标必须与唯一索引完全匹配

陷阱 2：在 DO UPDATE 中引用非冲突列

-- 唯一索引是 (email)-- 但更新时引用了 id（非冲突列）DOUPDATESETid=excluded.id-- ❌ 可能导致主键冲突！

✅ 解决：只更新非唯一约束列

陷阱 3：忽略 NULL 值的特殊性

-- 唯一索引允许 NULL 重复INSERTINTOt(nullable_col)VALUES(NULL);INSERTINTOt(nullable_col)VALUES(NULL);-- 不会冲突！

✅ 理解：PostgreSQL 中 NULL != NULL，唯一索引允许多个 NULL

陷阱 4：触发器行为异常

• BEFORE INSERT触发器在冲突时不会执行
• BEFORE UPDATE触发器在DO UPDATE时会执行
• 需要测试触发器逻辑是否符合预期

五、Python + SQLAlchemy 实战

5.1 原生 SQL 方式（推荐）

fromsqlalchemyimporttextdefupsert_user(session,email,name):stmt=text(""" INSERT INTO users (email, name, last_login) VALUES (:email, :name, NOW()) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, last_login = EXCLUDED.last_login RETURNING id; """)result=session.execute(stmt,{"email":email,"name":name})returnresult.scalar()

5.2 SQLAlchemy 2.0 Core 方式

fromsqlalchemyimportinsert stmt=(insert(users_table).values(email="alice@example.com",name="Alice").on_conflict_do_update(index_elements=["email"],set_=dict(name="Alice",last_login=func.now())).returning(users_table.c.id))

注意：SQLAlchemy ORM不直接支持 UPSERT，需用 Core 层或原生 SQL。