SQL ON CONFLICT 实现 Upsert

功能说明

示例假定 inventory 表以 sku 为主键，包含数量、价格、版本和更新时间。第一条语句一次处理三个 SKU：KB-01 与 MS-02 已存在，走 DO UPDATE 把来货数量加到库存并把版本加一；HD-03 不存在，直接插入。第二条语句模拟携带旧版本号的客户端，WHERE 条件不满足，因此不覆盖数据，RETURNING 返回零行。

关键代码

INSERT 使用 AS inv 给目标表起别名，EXCLUDED 代表本次提议的行。DO UPDATE 的 SET 可以同时引用 inv 与 EXCLUDED，从而实现“原库存加来货”的语义。版本条件写在 ON CONFLICT 的 WHERE 中，只有版本一致才更新，起到乐观锁作用。RETURNING 直接返回更新后的数量、价格与版本，调用方无需再查一次。新插入的行保留版本 1，只有更新路径才执行 version + 1。

使用注意

示例的补货是累加语义，重复提交同一批数据会把库存加两次，不具备幂等性；若需要幂等，应改用“设置为目标值”或单独记录幂等键。ON CONFLICT 在单行层面是原子的，但业务层仍需检查返回行数来判断是否发生版本冲突。并发更新同一 SKU 会串行化，热点行可能成为瓶颈。版本号必须由调用方正确传递，不能使用客户端时间戳代替。

验证方式

执行第一条语句应返回三行：KB-01 数量 22、版本 2；MS-02 数量 5、版本 2；HD-03 数量 7、版本 1。执行第二条语句应返回零行，随后查询可见 KB-01 的数量仍为 22。可把第二条的版本改为 2，确认更新成功并返回一行，从而验证条件分支。

代码示例

-- Schema assumptions (PostgreSQL 15+):
--   inventory(sku text PRIMARY KEY, quantity integer NOT NULL CHECK (quantity >= 0),
--             price_cents integer NOT NULL CHECK (price_cents >= 0),
--             version integer NOT NULL DEFAULT 1,
--             updated_at timestamptz NOT NULL DEFAULT now())
-- The TEMP table below mirrors that shape; no external service is required.

CREATE TEMP TABLE inventory (
    sku         text PRIMARY KEY,
    quantity    integer NOT NULL CHECK (quantity >= 0),
    price_cents integer NOT NULL CHECK (price_cents >= 0),
    version     integer NOT NULL DEFAULT 1,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO inventory (sku, quantity, price_cents, version) VALUES
    ('KB-01', 10, 4900, 1),
    ('MS-02', 4, 2500, 1);

-- One statement handles both paths: `excluded` is the proposed row.
-- Existing SKUs add incoming stock; new SKUs are inserted untouched.
INSERT INTO inventory AS inv (sku, quantity, price_cents, version)
VALUES
    ('KB-01', 12, 5200, 1),
    ('MS-02', 1, 2400, 1),
    ('HD-03', 7, 8900, 1)
ON CONFLICT (sku) DO UPDATE
SET quantity    = inv.quantity + EXCLUDED.quantity,
    price_cents = EXCLUDED.price_cents,
    version     = inv.version + 1,
    updated_at  = now()
WHERE EXCLUDED.version = inv.version
RETURNING sku, quantity, price_cents, version;

-- A stale writer carries version 0. It matches no current row, so the
-- update is skipped and RETURNING yields zero rows instead of overwriting.
INSERT INTO inventory AS inv (sku, quantity, price_cents, version)
VALUES ('KB-01', 5, 1, 0)
ON CONFLICT (sku) DO UPDATE
SET quantity    = EXCLUDED.quantity,
    price_cents = EXCLUDED.price_cents,
    updated_at  = now()
WHERE EXCLUDED.version = inv.version
RETURNING sku, quantity, price_cents, version;