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()才能读取数据。

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

• Go语言：必须使用db.QueryRow()。这个方法内部自动处理了单行结果集的逻辑，如果查询无结果，会返回sql.ErrNoRows错误。
• Python (psycopg2)：使用cursor.fetchone()，不要使用cursor.fetchall()。
• psql命令行：直接使用RETURNING *没问题，但在应用程序中不能假设总是返回多行。

还有一个最容易被忽略的关键点：即使语句触发了DO UPDATE（即执行了更新），RETURNING子句返回的也始终是当前行（即更新后的行）的值，而不是最初尝试插入的那些值。这一点在设计幂等性写入逻辑时，对于调试和理解行为至关重要。