PostgreSQL Upsert 冲突更新

示例说明

先查后插的竞态在并发下必然产生重复——upsert 把判定与写入合并成原子操作。冲突目标指向部分唯一索引：只有活跃订阅参与唯一性，软删除的历史行不挡新插入。DO UPDATE 的 EXCLUDED 是拟插入行的替身，批量场景下每行各自的值都从它取。

技术要点

• 冲突目标可指向部分唯一索引
• EXCLUDED 引用拟插入的值
• DO UPDATE 的 WHERE 充当守卫

代码解读

第一条 upsert 在冲突时累加座位数，WHERE 上限一千防超额——守卫不满足时该行保持原值，语句不报错；DO NOTHING 版本用于幂等重复写入直接忽略。批量同步从 staging 表 SELECT 多行插入，RETURNING 把实际生效的行返回给调用方核对。

使用建议

DO UPDATE 里的旧值用表名引用、新值用 EXCLUDED，方向别搞反。RETURNING 带版本号可做乐观并发。冲突目标写错会报 ambiguity，与索引谓词逐字对齐。

代码示例

-- PostgreSQL 活跃订阅表：软删除行的唯一性由部分索引保证
CREATE TABLE subscriptions (
  customer_id bigint NOT NULL,
  plan text NOT NULL,
  seats int NOT NULL,
  status text NOT NULL DEFAULT 'active',
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_subs_customer_active
  ON subscriptions (customer_id)
  WHERE status = 'active';

-- 幂等升级座位数：冲突时累加而非覆盖
INSERT INTO subscriptions (customer_id, plan, seats)
VALUES (42, 'pro', 10)
ON CONFLICT (customer_id) WHERE status = 'active'
DO UPDATE SET
  seats = subscriptions.seats + EXCLUDED.seats,
  updated_at = now()
WHERE subscriptions.seats + EXCLUDED.seats <= 1000;  -- 上限守卫

-- 不更新版本：纯粹忽略重复插入
INSERT INTO subscriptions (customer_id, plan, seats)
VALUES (43, 'basic', 5)
ON CONFLICT (customer_id) WHERE status = 'active'
DO NOTHING;

-- 批量同步：EXCLUDED 引用每行的拟插入值
INSERT INTO subscriptions (customer_id, plan, seats)
SELECT customer_id, 'pro', seat_count
FROM staging_seats
ON CONFLICT (customer_id) WHERE status = 'active'
DO UPDATE SET
  seats = EXCLUDED.seats,
  plan = EXCLUDED.plan,
  updated_at = now()
RETURNING customer_id, seats;