postgresql的on conflict必须明确指定唯一约束列或索引名，否则报错；do update中excluded.*需注意字段歧义；returning返回最终表状态；并发下可能因无前置校验导致更新丢失。
ON CONFLICT 子句里写错目标列会直接报错
PostgreSQL 的 ON CONFLICT 不是“自动猜主键”，它必须明确指定冲突发生的唯一约束或索引列。如果你只写了 ON CONFLICT 却没跟列名或约束名，或者写的列根本不在唯一约束里，就会报 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification。
实操建议：
- 用
\d 表名查看表的UNIQUE或PRIMARY KEY定义，把括号里的列原样写进ON CONFLICT (col1, col2) - 如果想复用已有唯一索引但不想暴露列名，可以写
ON CONFLICT ON CONSTRAINT 索引名（索引名通常带_key或_pkey后缀） - 别用主键 ID 做冲突目标却在 INSERT 里漏传 ID —— 这会导致插入新行而非更新，因为没触发冲突
DO UPDATE SET 里引用 excluded.* 要小心字段可见性
excluded 是个虚拟表，代表本次想插入但被拦下的那行数据。但它不是全局可见的：在 SET 子句里能直接用 excluded.col，但在 WHERE 条件里如果还混着原表字段，就得加表别名，否则可能报 column reference "xxx" is ambiguous。
常见错误现象：写 WHERE status != 'archived' 报错，因为 status 在原表和 excluded 里都存在。
实操建议：
- UPDATE 部分统一加表别名，比如
UPDATE my_table t SET name = excluded.name WHERE t.status != 'archived' - 避免在
WHERE里直接用excluded.col做逻辑判断——它只反映“这次想插什么”，不代表业务意图；真正该判的是原表状态 - 如果要根据新旧值差异做更新（比如只在新值非空时覆盖），写成
SET col = COALESCE(excluded.col, t.col)
RETURNING 和 DO UPDATE 一起用时，返回的是更新后的行
RETURNING * 在 UPSERT 中不会返回“原本想插入的那行”，而是返回执行完 DO UPDATE 后最终存进表里的那行。如果没触发更新（即走 INSERT 分支），返回的就是新插入的行；如果触发了更新，返回的就是更新后的结果行。
使用场景：适合做“查+改+拿结果”原子操作，比如生成订单并立刻返回完整记录，或幂等接口中确保客户端拿到最终态。
性能影响：
-
RETURNING本身不显著拖慢，但若返回大字段（如 JSONB、TEXT），网络和内存开销会上升 - 别在高频写入场景里无脑
RETURNING *—— 只取需要的字段，比如RETURNING id, updated_at - 注意：
RETURNING拿不到excluded的值，它只反映表最终状态
并发下 ON CONFLICT 可能导致 unexpected 更新丢失
PostgreSQL 的 ON CONFLICT 是基于索引扫描+行锁实现的，不是乐观锁。两个事务同时尝试插入同一冲突键时，后到的那个会被阻塞，等前一个提交后再判断是否还需更新。这本身没问题，但容易被忽略的是：如果 DO UPDATE 里有依赖当前时间或序列的逻辑（比如 updated_at = NOW() 或 version = version + 1），两次并发请求可能都成功更新，且都返回最新值 —— 表面看没问题，但中间一次业务逻辑可能被覆盖。
容易踩的坑：
- 用
version = version + 1做乐观控制，却没在WHERE里校验原始 version —— 这会让并发更新跳过检查 - 在
DO UPDATE里调用gen_random_uuid()或CURRENT_TIMESTAMP，不同事务得到不同结果，但应用层无法感知谁先谁后 - 把 UPSERT 当作“条件更新”的替代品，而没意识到它没有 WHERE 的前置读取语义
事情说清了就结束
