PostgreSQL如何实现UPSERT？ON CONFLICT DO UPDATE与MERGE语句详解

UPSERT是数据库领域一个非常经典的需求：插入一条数据时，如果唯一键已存在，就转而执行更新操作。PostgreSQL从9.5版本开始提供了原生的ON CONFLICT DO UPDATE语法，而在PostgreSQL 15中又引入了SQL标准的MERGE语句，两种方式各有侧重。本文将从语法细节、典型用法、并发安全和方案选型几个角度，系统地讲清楚这两种实现方式。

ON CONFLICT DO UPDATE基础语法与核心概念

ON CONFLICT是PostgreSQL独有的INSERT扩展语法，习惯上被称为UPSERT。它的基本结构是在普通INSERT语句后追加冲突处理子句，冲突目标可以是一个唯一索引的字段，也可以是一个具体的唯一约束名称。先看一个最简单的例子，假设有一张用户表，主键为id：

CREATE TABLE users (
    id      serial PRIMARY KEY,
    email   text UNIQUE NOT NULL,
    name    text,
    updated_at timestamptz DEFAULT now()
);

-- 基本UPSERT：email冲突时更新name
INSERT INTO users (email, name)
VALUES ('test@ipipp.com', '张三')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

这里的关键点有两个。第一是冲突目标(email)，它必须精确匹配表上的唯一约束或唯一索引所覆盖的字段，不能随便写一个普通字段，否则会报错提示不存在对应的唯一约束。第二是EXCLUDED这个特殊的行引用，它代表本次尝试插入但由于冲突而被排除的那一行，可以理解为正在插入的新值。上面的语句中，EXCLUDED.name就是本次想要插入的name值，而users.name（或直接写name）则是表中已存在的旧值。

理解新旧值的引用非常重要。在DO UPDATE SET子句中，不加前缀的字段名指向表中已存在的行，加EXCLUDED前缀则指向新插入的行。例如实现一个计数器累加的场景：每次插入一条记录，若已存在则把次数加一，就可以写成SET count = users.count + EXCLUDED.count。另外，如果只想在满足条件时才更新，可以在DO UPDATE后面追加WHERE子句，例如只在数据确实发生变化时才更新，避免无意义的写放大：

INSERT INTO users (email, name)
VALUES ('test@ipipp.com', '张三三')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now()
WHERE users.name IS DISTINCT FROM EXCLUDED.name;

这个WHERE条件里用的是IS DISTINCT FROM而不是等号，好处是可以正确处理NULL值，因为普通等号遇到NULL会返回NULL导致条件不成立。加上这个判断后，如果新旧name完全相同，语句会静默跳过更新，触发行数也会如实反映实际变化，对依赖返回值做统计的业务很有用。

多字段唯一约束、部分索引与常见报错处理

实际业务中唯一约束往往不止一个字段。比如订单明细表可能要求订单ID加商品ID的组合唯一，这时冲突目标要写成多个字段的组合：

CREATE TABLE order_items (
    order_id   bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity   int NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 88, 2)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;

还有一种容易被忽略的情况是基于部分索引的UPSERT。假设某表用软删除，deleted_at为NULL表示有效记录，唯一索引只对有效记录生效，建索引时带了WHERE条件。此时ON CONFLICT子句必须复述相同的WHERE谓词，否则PostgreSQL无法识别你要匹配哪个索引。另外也可以不写字段而直接指定约束名：ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ...，这种方式在约束改名后会导致SQL失效，一般推荐写字段列表，可维护性更好。

常见报错主要有两类。一是there is no unique or exclusion constraint matching the ON CONFLICT specification，说明冲突目标与表上的唯一约束对不上，需要检查字段顺序和索引是否为唯一索引，普通B-tree索引不能作为冲突目标。二是ON CONFLICT DO UPDATE command cannot affect row a second time，这通常发生在同一条INSERT语句的VALUES列表中有重复键，比如批量插入时两行数据的主键相同，PostgreSQL无法在同一语句中对同一行更新两次。解决办法是在SQL层面先去重，例如用DISTINCT ON或者ROW_NUMBER取每组最新一条后再插入。

并发场景下ON CONFLICT的优势也非常明显。多个会话同时插入相同键时，后到的事务不会收到唯一约束冲突错误，而是会等待先到的会话提交，然后自动转为更新路径，这正是它比先SELECT再判断INSERT或UPDATE的应用层写法更可靠的原因，天然避免了竞态条件，也减少了重试逻辑。

MERGE语句与ON CONFLICT的对比与选型

PostgreSQL 15引入了SQL标准的MERGE语句，它以目标表和源头数据为基础，支持MATCHED（匹配到）和NOT MATCHED（未匹配）两类动作，并且动作不限于UPDATE和INSERT，还可以是DELETE和DO NOTHING，表达能力比ON CONFLICT更强。一个等价于前面UPSERT的MERGE写法如下：

MERGE INTO users AS t
USING (VALUES ('test@ipipp.com', '张三')) AS s(email, name)
ON t.email = s.email
WHEN MATCHED THEN
    UPDATE SET name = s.name, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (email, name) VALUES (s.email, s.name);

两者该怎么选可以从几个维度考虑。第一，同步删除：MERGE可以在匹配时执行DELETE，实现删除目标表中源头已不存在的数据，而ON CONFLICT无法删除。第二，多条件分支：MERGE支持带WHEN子句的多条MATCHED规则，例如根据金额大小决定更新还是删除，ON CONFLICT只有一个更新分支加一个WHERE。第三，并发插入防护：MERGE的NOT MATCHED分支在并发插入相同键时仍可能抛出唯一约束冲突错误，不像ON CONFLICT那样内置重试语义，需要业务层自行处理或加锁。第四，性能：纯批量UPSERT场景下ON CONFLICT通常更简洁高效，尤其是配合COPY加临时表的做法。

一个典型的高性能批量同步模式是：先用COPY把外部数据灌入UNLOGGED的临时表，再对临时表做去重，最后一条INSERT SELECT ON CONFLICT DO UPDATE完成合并。这种组合在千万级数据量下依然稳定，避免了逐行MERGE的开销。而如果是需要同时处理插入、更新、删除的完整数据同步任务，且使用PostgreSQL 15及以上版本，MERGE语句写起来更直观，一个语句就能覆盖全部逻辑。

总结一下：日常的单表幂等写入、缓存回写、计数累加，首选ON CONFLICT DO UPDATE，语法简单且并发安全；涉及多表关联的复杂合并、需要按条件删除或多个分支动作时，选择MERGE。掌握EXCLUDED引用、WHERE条件更新和约束匹配规则这三个要点，绝大多数UPSERT相关的问题都能迎刃而解。

PostgreSQL UPSERTON CONFLICT DO UPDATEMERGE语句修改时间：2026-08-31 21:08:41