PostgreSQL 没有 UPSERT 关键字。你要写的是 INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column。当新行会违反这一列上的唯一约束时,PostgreSQL 就改为更新已有的行。DO NOTHING 则直接跳过。
A-100 已经存在,所以它的价格变成了 12.5。B-200 是新的,于是被插入。一条语句同时处理了两种情况,而且其他会话同时插入相同的键时,它依然正确。
插入或更新一行
这条语句由三部分组成:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- 冲突目标指出某个唯一索引或唯一约束的列,比如主键。它必须和某个约束完全一致,否则 PostgreSQL 会报错“there is no unique or exclusion constraint matching the ON CONFLICT specification”。也可以直接写约束名:
ON CONFLICT ON CONSTRAINT products_pkey。 EXCLUDED是你尝试插入的那一行。EXCLUDED.price是新的价格,products.price是已经存储的价格。DO UPDATE SET只修改你列出的列。已有行的其他列保持原值。
带 WHERE 子句的唯一索引(部分索引)也可以作为冲突目标,只要重复写出它的条件:ON CONFLICT (email) WHERE deleted_at IS NULL。
用 DO NOTHING 跳过重复
DO NOTHING 插入新的行,丢掉冲突的行,不会报错。不写冲突目标时,违反任何唯一约束的行都会被跳过。
RETURNING 只列出了 lin@example.com。被跳过的行不会被返回,所以 DO NOTHING ... RETURNING 能告诉你哪些行是新插入的。
累加计数器
如果要增加已存储的值而不是替换它,就把 EXCLUDED 加到当前值上。当前值要用表名限定。在 DO UPDATE 中,单独写 views 既能匹配表也能匹配 EXCLUDED,PostgreSQL 会报错“column reference "views" is ambiguous”并停止。
第一条语句以 1 插入这一行,后两条在它上面累加,所以 /home 最终是 3。
只在值有变化时更新
写在 DO UPDATE SET 后面的 WHERE 会逐行决定是否执行更新。条件为假时,这一行保持不变,既不算插入也不算更新。可以用它只保留最新的数据,或者在没有变化时避免写入新的行版本。
A-100 保持 10,因为传入的行更旧。B-200 变成了 25。要跳过会写入相同值的更新,用 WHERE prices.price IS DISTINCT FROM EXCLUDED.price,它也能正确处理 NULL。
区分插入的行和更新的行
RETURNING 在 upsert 中的用法和普通插入一样。要区分两种情况,可以读取系统列 xmax,对于本条语句插入的行,它的值是 0:
A-100 返回 8,inserted 为 false;C-300 返回 7,为 true。xmax 是内部细节,不是有文档保证的 API,所以只把它当作一种便利手段。完整的子句介绍见 RETURNING 页面。
你会遇到的错误
- “ON CONFLICT DO UPDATE command cannot affect row a second time”:同一个键在一条语句的
VALUES或SELECT中出现了两次。先去掉重复,比如用SELECT DISTINCT ON (sku) ...。 - “column reference is ambiguous”:在
SET表达式和WHERE中用表名限定列,像上面那样。 - 标识列或 serial id 出现空缺:变成更新的插入已经从序列中取走了一个值,所以 id 会跳号。这是正常的。
MERGE(PostgreSQL 15 及以上)也能插入或更新,但它不能防止两个会话同时插入相同的键。按键做 upsert,请用 ON CONFLICT。所有选项见官方 INSERT 参考文档。
常见问题
PostgreSQL 有 UPSERT 语句吗?
没有 UPSERT 关键字。PostgreSQL 9.5 新增了 INSERT ... ON CONFLICT,它插入一行;如果插入会违反唯一约束,就更新已有的行(DO UPDATE)或者跳过它(DO NOTHING)。
并发插入时 INSERT ... ON CONFLICT 安全吗?
安全。即使其他会话在同一时刻插入相同的键,每个待插入的行最终都会变成一次插入或一次更新。在应用代码中先 SELECT 再 INSERT 或 UPDATE,做不到这样的保证。
upsert 应该用 MERGE 还是 ON CONFLICT?
按键插入或更新时用 ON CONFLICT。MERGE(PostgreSQL 15 及以上)能处理更多情况,比如删除匹配的行。但在并发插入相同键时,它可能会因唯一约束冲突而失败,而 ON CONFLICT 不会。
为什么会报错“there is no unique or exclusion constraint matching the ON CONFLICT specification”?
ON CONFLICT (...) 中的列必须和某个唯一索引或唯一约束完全一致。在这些列上添加 UNIQUE 约束或唯一索引,或者改成写一个已有约束的列。