首页 > 数据库 >PostgreSQL如何高效执行UPSERT操作_利用ON CONFLICT指令

PostgreSQL如何高效执行UPSERT操作_利用ON CONFLICT指令

来源：互联网 2026-04-29 18:52:02

PostgreSQL ON CONFLICT：唯一可靠的原子UPSERT操作指南在PostgreSQL的世界里，如果你想实现“有则更新，无则插入”的UPSERT操作，ON CONFLICT是那条唯一可靠、原子且可预测的路径。至于那些在业务层自己写的“先查询，再决定插入或更新”的逻辑，在并发场景下几

PostgreSQL ON CONFLICT：唯一可靠的原子UPSERT操作指南

在PostgreSQL的世界里，如果你想实现“有则更新，无则插入”的UPSERT操作，ON CONFLICT是那条唯一可靠、原子且可预测的路径。至于那些在业务层自己写的“先查询，再决定插入或更新”的逻辑，在并发场景下几乎注定会出问题，建议直接放弃尝试。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

ON CONFLICT 必须基于唯一约束，否则直接报错

这里有个关键点需要明确：当你写下ON CONFLICT (email)时，PostgreSQL并不会自动为你创建索引。它只会检查email这一列是否已经定义了UNIQUE约束或是PRIMARY KEY。如果没有，那么等待你的将是明确的错误提示：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

哪些是常见的误解和误操作呢？

对着一张表的普通INDEX（非唯一索引）使用ON CONFLICT——行不通。
对着一个仅有NOT NULL属性的列使用——同样行不通。
表刚创建完，忘了给目标列加上UNIQUE(email)约束就直接执行SQL——结果就是报错。

验证方法其实很简单：在psql命令行中执行\d 表名，确认目标列旁边清晰地标记着UNIQUE或PK。

多唯一约束时，必须显式指定冲突目标

想象一张表同时拥有email UNIQUE、phone UNIQUE和id PRIMARY KEY三个唯一约束。如果你只写了ON CONFLICT (id)，那么当email发生冲突时，语句依然会报错，而不会自动回退到其他约束进行处理。

正确的做法是二选一：

使用列名：如果冲突只可能来自主键，那就明确指定ON CONFLICT (id)。
使用约束名：这种方式通常更安全，尤其当你需要响应特定列（如email）的冲突时。首先查询约束名：SELECT conname FROM pg_constraint WHERE conrelid = 'users'::regclass AND contype = 'u';，然后在语句中引用：ON CONFLICT ON CONSTRAINT users_email_key。
对于联合唯一约束，逻辑相同：例如定义了UNIQUE(user_id, product_id)，就必须写ON CONFLICT (user_id, product_id)。

DO UPDATE 里用 WHERE 条件要格外小心

很多人习惯在DO UPDATE后面加上WHERE products.status = 'active'这样的条件，意图是“只更新状态为有效的商品”。但这里有个至关重要的细节：这个WHERE子句是作用于原表行（即发生冲突的那一行）的，而不是作用于准备插入的新数据。一旦条件不成立，整个DO UPDATE操作就会被跳过——结果就是既没有插入新行，也没有更新旧行。更棘手的是，语句还会返回成功（例如INSERT 0 1），这种“静默失效”非常隐蔽，难以排查。

容易踩到的坑包括：

忘记加WHERE条件，导致意外覆盖了历史状态。
加了WHERE条件，却没意识到它可能导致整个UPSERT操作静默失败。
在DO UPDATE SET中引用EXCLUDED虚拟表来获取新值时，误写成MySQL风格的VALUES()语法（PostgreSQL不支持这种写法）。

来看一个正确的写法示例：

INSERT INTO products (sku, price, status) VALUES ('IPHONE_15', 7999, 'active') ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price, updated_at = NOW() WHERE products.status = 'active';

想拿到插入或更新后的 ID？RETURNING 必须配 QueryRow

这个场景很常见：在Go或Python程序中，开发者使用db.Query(... RETURNING id)，然后直接调用rows.Scan(&id)，却发现返回的id总是0。问题出在哪里？因为RETURNING子句最多只返回一行，而像Go的db.Query()方法要求必须先调用rows.Next()才能读取数据。

真正安全的做法只有这一种：