INSERT ... ON CONFLICT 的 Upsert

概念概述

INSERT … ON CONFLICT 的 Upsert（Upsert with INSERT … ON CONFLICT）指的是：Upsert 在唯一约束冲突时插入或更新行，把易竞态的读改写合并为单条原子语句，是幂等写入的常见写法。

从更严格的定义看，An upsert is a single statement that inserts a new row and, if a uniqueness constraint would be violated, instead updates the existing row or ignores the conflict. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It is the standard way to implement idempotent writes, counters, and last-writer-wins state without a separate select followed by insert or update. 它与 The RETURNING Clause、GIN Indexes for Composite Values、Trigram Indexes for Fuzzy Matching 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• upsert never needs a unique constraint
• on conflict do update always returns the newly updated row to every caller

需要避免的反模式

• doing a select then an insert in application code under concurrency
• including the target column in the update set and creating a no-op

实践要点

1. The conflict target must match a unique index, and the update clause can reference the proposed values by their virtual row name, so both insert and update paths use one statement shape.
2. Because the operation is atomic at the statement level, concurrent callers do not lose data the way a read-then-write in application code can.
3. Under high concurrency, competing upserts can still block on the same key, so batch writes and keep transactions short to avoid lock convoying.

参考来源

https://www.postgresql.org/docs/current/sql-insert.html

代码示例

INSERT INTO products (product_id, product_name, unit_price, stock_quantity, category_id)
VALUES
    (101, 'Wireless Mouse', 29.99, 150, 1),
    (102, 'USB Keyboard', 49.99, 80, 1),
    (103, 'HD Monitor', 299.99, 25, 1),
    (104, 'Webcam', 89.99, 60, 1)
ON CONFLICT (product_id)
DO UPDATE SET
    product_name = EXCLUDED.product_name,
    unit_price = EXCLUDED.unit_price,
    stock_quantity = products.stock_quantity + EXCLUDED.stock_quantity
WHERE products.unit_price != EXCLUDED.unit_price
   OR products.stock_quantity < EXCLUDED.stock_quantity;

-- Insert from a SELECT query
INSERT INTO order_summary (order_id, total_items, total_amount)
SELECT
    order_id,
    SUM(quantity) AS total_items,
    SUM(quantity * unit_price) AS total_amount
FROM order_items
GROUP BY order_id
ON CONFLICT (order_id) DO NOTHING;

-- 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;