UPSERT 的意思是"插入,如果已存在则更新"。PostgreSQL 通过 INSERT ... ON CONFLICT 来实现这一功能——插入一行,但如果它会违反唯一约束,则改为更新已有的行(或者什么都不做)。它以原子方式处理了常见的"插入或更新"需求。
UPSERT 解决的问题
You want to insert a row, but it might already exist (by a unique key):
❌ a plain INSERT fails with a unique-violation error if it exists
❌ check-then-insert (SELECT, then INSERT or UPDATE) has a RACE CONDITION
(another transaction could insert between your check and insert)
→ ON CONFLICT does it atomically in ONE statement (no race condition)
