PHP小码农

Appearance

UPSERT（INSERT ... ON CONFLICT） ​

"存在就更新，不存在就插入"是同步类业务里最普遍的需求。PostgreSQL 用 INSERT ... ON CONFLICT 提供了原生 UPSERT 能力，无需应用层"先查后写"，从根上消除了竞态窗口。本节讲清冲突目标、DO NOTHING 与 DO UPDATE、EXCLUDED 伪表、条件更新，以及如何区分"本次是新插还是更新"。

前置知识

阅读本节前，建议先了解：插入数据（INSERT）

基础概念 ​

UPSERT = UPdate + inSERT，指"尝试插入；若与既有唯一性约束冲突，则改为更新"。PostgreSQL 从 9.5 起支持 ON CONFLICT，它是原子的：在冲突发生时，数据库在内部完成"发现冲突 → 更新既有行"，不会像"先 SELECT 再决定"那样在两个事务之间存在夹缝。

冲突动作语法命中冲突时影响行数能否返回冲突行
忽略ON CONFLICT (...) DO NOTHING什么都不做只计真正插入的行否
更新ON CONFLICT (...) DO UPDATE SET ...改写既有行插入 + 更新的行是
无条件忽略ON CONFLICT DO NOTHING任意唯一约束冲突都跳过只计真正插入的行否

几个关键术语：

  • 冲突目标（Conflict Target）：ON CONFLICT (col) 括号内的列或表达式。它必须能唯一确定一个唯一索引 / 唯一约束，否则报 there is no unique or exclusion constraint matching the ON CONFLICT specification。
  • EXCLUDED 伪表：代表"本次本来要插入、但因为冲突没插进去的那一行"。写法 EXCLUDED.列名 取的是新值，直接写列名取的是表里已有的旧值。这是 UPSERT 里最容易搞反的地方。
  • 不加冲突目标的 DO NOTHING：ON CONFLICT DO NOTHING（括号可省）对任何唯一冲突都跳过，无需指名具体约束。但 DO UPDATE 必须给出冲突目标（因为要确定用哪个唯一性判定）。
  • ON CONFLICT 的前提是唯一索引：普通索引、非唯一约束不参与。冲突判定只能建立在 UNIQUE / PRIMARY KEY / EXCLUDE 约束或唯一索引之上。
  • 部分唯一索引（Partial Unique Index）：只要冲突目标能匹配该部分索引的谓词，ON CONFLICT 也能命中。这是实现"软删除下唯一"的关键技巧。
  • 幂等（Idempotency）：同一批数据重复执行多次，最终结果与执行一次相同。UPSERT 是达成幂等最常用的手段。

语法与用法 ​

完整语法骨架：

text
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( ... ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING ... ]

where conflict_target is one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ]
      [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

两个最常用的形态：

sql
-- 1. 冲突就跳过（幂等插入）
INSERT INTO inventory (sku, qty)
VALUES ('SKU-1', 10)
ON CONFLICT (sku) DO NOTHING;

-- 2. 冲突就更新（真正的 UPSERT）
INSERT INTO inventory (sku, qty)
VALUES ('SKU-1', 10)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + EXCLUDED.qty;

EXCLUDED 与表名的取值对照：

表达式含义在 DO UPDATE 中
EXCLUDED.qty本次想插入的新值可用于赋值右侧
inventory.qty表中已存在的当前值可用于赋值右侧与 WHERE
qty（裸列名）默认解析为表中已有值右侧引用旧值
EXCLUDED 整行本次候选行可整体用于表达式

下面用一张贯穿本节的示例表：

sql
-- 示例：SKU 库存表 + 每日同步场景
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
  sku         text          PRIMARY KEY,
  name        text          NOT NULL,
  qty         integer       NOT NULL DEFAULT 0 CHECK (qty >= 0),
  price       numeric(12,2) NOT NULL CHECK (price >= 0),
  updated_at  timestamptz   NOT NULL DEFAULT now()
);

-- 另一张用于演示"复合唯一键"的表
DROP TABLE IF EXISTS daily_stats;
CREATE TABLE daily_stats (
  stat_date   date    NOT NULL,
  channel     text    NOT NULL,
  pv          bigint  NOT NULL DEFAULT 0,
  uv          bigint  NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, channel)
);

详细说明 ​

DO NOTHING：最简单的幂等写入 ​

DO NOTHING 的语义是"撞了就当这行没提交过"。它不需要知道具体是哪个约束冲突（不写括号时），也不会触碰既有数据。

sql
-- 准备数据
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘',   120, 349.00),
  ('SKU-1002', '无线鼠标',   300, 129.00)
ON CONFLICT (sku) DO NOTHING;
text
INSERT 0 2
sql
-- 重复提交同一批数据（模拟任务重跑）
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘', 120, 349.00),
  ('SKU-1003', 'USB-C 扩展坞', 80, 199.00)
ON CONFLICT (sku) DO NOTHING
RETURNING sku, qty;
text
  sku     | qty
----------+-----
 SKU-1003 |  80
(1 row)

INSERT 0 1
-- 注意：SKU-1001 已存在被跳过，RETURNING 只返回 1 行。
-- 但"被跳过的行"不等于"没插进去"——它也没有更新既有行的 price。

DO NOTHING 的一个实用变体是不指定冲突目标，它对所有唯一冲突都生效，适合"我不确定会撞哪个约束"的场景：

sql
-- 任何唯一约束冲突都跳过
INSERT INTO inventory (sku, name, qty, price)
VALUES ('SKU-1001', '机械键盘', 999, 1.00)
ON CONFLICT DO NOTHING;

DO UPDATE 与 EXCLUDED ​

DO UPDATE 才是真正的 UPSERT。它必须给出冲突目标，并在 SET 中描述如何用新旧值计算新行。

sql
-- 冲突时：数量累加，价格覆盖，时间戳刷新
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘', 50, 339.00),     -- 已存在：qty 累加、price 覆盖
  ('SKU-1004', '显示器支架', 45, 259.00)    -- 不存在：直接插入
ON CONFLICT (sku) DO UPDATE
  SET qty        = inventory.qty + EXCLUDED.qty,   -- 旧值 + 新值
      name       = EXCLUDED.name,                  -- 覆盖为新值
      price      = EXCLUDED.price,                 -- 覆盖为新值
      updated_at = now()
RETURNING sku, qty, price;
text
   sku    | qty | price
----------+-----+--------
 SKU-1001 | 170 | 339.00
 SKU-1004 |  45 | 259.00
(2 rows)

INSERT 0 2

注意 EXCLUDED 与裸列名的区别，这是 UPSERT 里最常见的错误来源：

sql
-- 正确：qty 累加（旧值 + 新值）
SET qty = inventory.qty + EXCLUDED.qty;

-- 也是正确：裸列名默认指表中原值
SET qty = qty + EXCLUDED.qty;

-- 错误理解：以为 EXCLUDED.qty 是旧值
SET qty = EXCLUDED.qty + EXCLUDED.qty;   -- 结果是把新值加了两遍

当表有别名时，必须用别名而不是表名：

sql
-- 表带别名时，用别名限定旧值
INSERT INTO inventory AS inv (sku, name, qty, price)
VALUES ('SKU-1002', '无线鼠标', 10, 119.00)
ON CONFLICT (sku) DO UPDATE
  SET qty = inv.qty + EXCLUDED.qty;
-- 此时写 inventory.qty 会报错：missing FROM-clause entry for table "inventory"

条件更新：只在必要时才写 ​

DO UPDATE 支持两个 WHERE：

  • 冲突目标后的 WHERE：作用于索引谓词，用于匹配部分唯一索引。
  • DO UPDATE SET ... WHERE：作用于要不要真的更新这行。条件不成立时，该行既没插入也没更新。

后者是避免"无意义写入"的关键手段。每次 UPDATE 都会产生新的行版本（死元组），若同步任务每次都用相同数据覆盖相同值，会造成表膨胀与 WAL 浪费。

sql
-- 只有当价格变了才更新，否则该行被静默跳过（不产生新版本）
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘', 0, 339.00)
ON CONFLICT (sku) DO UPDATE
  SET price      = EXCLUDED.price,
      updated_at = now()
WHERE inventory.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price;
text
 sku | price
-----+-------
(0 rows)

INSERT 0 0
-- 价格没变，因此没有行被更新，RETURNING 返回空集

IS DISTINCT FROM 在这里比 <> 更好用：当两侧可能为 NULL 时，NULL <> NULL 结果是 NULL（不成立），而 NULL IS DISTINCT FROM NULL 是 false，语义更符合预期。

区分新插与更新 ​

业务上经常需要知道"这一行到底是新插入的，还是被更新的"（例如统计每日新增 UV）。PostgreSQL 提供了一个技巧：用隐藏系统列 xmax 判断。

  • 真正被 INSERT 的新行：xmax = 0。
  • 因冲突被 UPDATE 的行：xmax <> 0（xmax 记录了删除/锁定它的事务号）。
sql
INSERT INTO daily_stats (stat_date, channel, pv, uv) VALUES
  ('2026-09-24', 'web', 1000, 300),
  ('2026-09-24', 'app', 2000, 800)
ON CONFLICT (stat_date, channel) DO UPDATE
  SET pv = daily_stats.pv + EXCLUDED.pv,
      uv = daily_stats.uv + EXCLUDED.uv
RETURNING stat_date, channel, pv, uv, (xmax = 0) AS is_insert;
text
 stat_date  | channel |  pv   |  uv  | is_insert
------------+---------+-------+------+-----------
 2026-09-24 | web     |  1000 |  300 | t
 2026-09-24 | app     |  2000 |  800 | t
(2 rows)

-- 再次执行同样的语句
sql
INSERT INTO daily_stats (stat_date, channel, pv, uv) VALUES
  ('2026-09-24', 'web', 500, 100)
ON CONFLICT (stat_date, channel) DO UPDATE
  SET pv = daily_stats.pv + EXCLUDED.pv,
      uv = daily_stats.uv + EXCLUDED.uv
RETURNING stat_date, channel, pv, uv, (xmax = 0) AS is_insert;
text
 stat_date  | channel |  pv   |  uv  | is_insert
------------+---------+-------+------+-----------
 2026-09-24 | web     |  1500 |  400 | f
(1 row)
-- is_insert = f 表示这行是命中冲突后被更新的

xmax = 0 是社区广泛使用的技巧，但它依赖存储实现细节。更"正规"的替代方案是用 RETURNING 配合一个额外的比较表达式来判断。

冲突目标的各种写法 ​

冲突目标可以是列名、列的组合、表达式，甚至直接指定约束名。

单列，对应单列唯一约束：

sql
INSERT INTO inventory (sku, name, qty, price)
VALUES ('SKU-2001', '测试品', 1, 1.00)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

多列组合，对应复合唯一约束（顺序需与索引列顺序一致才能匹配 B-Tree 索引）：

sql
INSERT INTO daily_stats (stat_date, channel, pv, uv)
VALUES ('2026-09-25', 'web', 10, 5)
ON CONFLICT (stat_date, channel) DO UPDATE
  SET pv = EXCLUDED.pv, uv = EXCLUDED.uv;

约束名，语法更明确，不受列顺序影响：

sql
INSERT INTO daily_stats (stat_date, channel, pv, uv)
VALUES ('2026-09-26', 'app', 20, 8)
ON CONFLICT ON CONSTRAINT daily_stats_pkey DO UPDATE
  SET pv = EXCLUDED.pv, uv = EXCLUDED.uv;

表达式索引，冲突目标是索引里定义的表达式：

sql
-- 建一个基于小写邮箱的唯一索引
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

INSERT INTO users (email, name)
VALUES ('Alice@Example.com', 'Alice')
ON CONFLICT (lower(email)) DO UPDATE
  SET name = EXCLUDED.name;

部分唯一索引，配合冲突目标后的 WHERE 谓词：

sql
-- 业务要求：同一租户下未删除的用户名唯一
CREATE UNIQUE INDEX idx_uniq_username_active
  ON users (tenant_id, username)
  WHERE deleted_at IS NULL;

-- UPSERT 时必须带上同样的谓词
INSERT INTO users (tenant_id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON CONFLICT (tenant_id, username) WHERE deleted_at IS NULL
DO UPDATE SET email = EXCLUDED.email;

常见报错与原因：

报错信息原因解决
there is no unique or exclusion constraint matching the ON CONFLICT specification冲突目标没有对应的唯一索引补建唯一索引，或调整冲突目标
ON CONFLICT DO UPDATE requires inference specification or constraint nameDO UPDATE 没写冲突目标补上 (col) 或 ON CONSTRAINT name
invalid reference to the indexed column表达式索引的冲突目标写法不对冲突目标中用完全相同的表达式
column reference "qty" is ambiguous未加表限定导致歧义用表名/别名或 EXCLUDED 显式限定

部分更新与列级保护 ​

UPSERT 常用模式是"新数据只覆盖它真正关心的字段，其余列保留原值"。做法很简单：不在 SET 中出现的列就不会变。

sql
-- 同步任务只带了价格，不要覆盖本地维护的 name 与 qty
INSERT INTO inventory (sku, name, qty, price)
VALUES ('SKU-1001', '临时占位名', 0, 399.00)
ON CONFLICT (sku) DO UPDATE
  SET price      = EXCLUDED.price,      -- 只更新价格
      updated_at = now()
RETURNING sku, name, qty, price;
text
   sku    |   name   | qty | price
----------+----------+-----+--------
 SKU-1001 | 机械键盘 | 170 | 399.00
(1 row)
-- name 与 qty 保留原值，只有 price 被覆盖

反过来，若希望"新值为空时不覆盖旧值"，用 COALESCE 或 CASE：

sql
ON CONFLICT (sku) DO UPDATE
  SET name  = COALESCE(EXCLUDED.name, inventory.name),
      price = COALESCE(EXCLUDED.price, inventory.price);

或者用 NULLIF 过滤掉业务上的"空串"：

sql
ON CONFLICT (sku) DO UPDATE
  SET name = COALESCE(NULLIF(EXCLUDED.name, ''), inventory.name);

并发与序列化失败 ​

ON CONFLICT 解决了竞态，但并不消除并发冲突本身。在 READ COMMITTED 隔离级别下，两个事务同时 DO UPDATE 同一行时，后到的那个会看到对方提交后的版本并按 EXCLUDED 重新计算——这是"最后一次写入获胜并基于最新值累加"的语义，通常正是我们想要的。

但在 REPEATABLE READ / SERIALIZABLE 下，可能抛出：

text
ERROR:  could not serialize access due to concurrent update

应用层必须把这类错误视为可重试错误，捕获后重试整个事务。

另一个经典问题：UPSERT 会消耗序列号。ON CONFLICT 的插入路径必须先求值候选行，nextval 会被调用，即使最终因冲突走的是更新分支，那个序号也被浪费掉了（序列不属于事务，回滚不退还）。这会导致 IDENTITY / serial 主键出现空洞——这是正常现象，不是 bug。

sql
-- 观察：命中冲突时序号依然被消耗
INSERT INTO orders (order_no, user_email, amount)
VALUES ('ORD-5002', 'bob@example.com', 99.00)
ON CONFLICT (order_no) DO UPDATE SET amount = EXCLUDED.amount
RETURNING id, order_no;
-- id 是一个"新"的序号，但最终这行走的是更新分支，序号作废

若冲突率极高（例如 > 50%），可以先用 SELECT 分流、再分别 INSERT / UPDATE，或用 MERGE——但要权衡复杂度。见 MERGE（PostgreSQL 15+）。

实战示例 ​

下面模拟"每天从上游同步 SKU 价格与库存"的完整链路：建表 → 首次全量导入 → 增量幂等同步 → 条件更新抑制无意义写入 → 区分新增与更新 → 校验序号水位。

sql
-- 步骤 1：准备示例表
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
  sku         text          PRIMARY KEY,
  name        text          NOT NULL,
  qty         integer       NOT NULL DEFAULT 0 CHECK (qty >= 0),
  price       numeric(12,2) NOT NULL CHECK (price >= 0),
  synced_at   timestamptz   NOT NULL DEFAULT now()
);

CREATE INDEX idx_inventory_price ON inventory (price);
text
DROP TABLE
CREATE TABLE
CREATE INDEX
sql
-- 步骤 2：首次全量导入（表中无数据，全部走插入分支）
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘',     120, 349.00),
  ('SKU-1002', '无线鼠标',     300, 129.00),
  ('SKU-1003', 'USB-C 扩展坞',  80, 199.00)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name, qty = EXCLUDED.qty, price = EXCLUDED.price,
      synced_at = now()
RETURNING sku, qty, price, (xmax = 0) AS is_insert;
text
   sku    | qty | price  | is_insert
----------+-----+--------+-----------
 SKU-1001 | 120 | 349.00 | t
 SKU-1002 | 300 | 129.00 | t
 SKU-1003 |  80 | 199.00 | t
(3 rows)

INSERT 0 3
sql
-- 步骤 3：次日增量同步（两条已有 + 一条新增），库存做增量累加、价格做覆盖
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘',     30, 339.00),
  ('SKU-1002', '无线鼠标',     50, 129.00),
  ('SKU-1004', '显示器支架',   45, 259.00)
ON CONFLICT (sku) DO UPDATE
  SET qty        = inventory.qty + EXCLUDED.qty,   -- 入库累加
      name       = EXCLUDED.name,
      price      = EXCLUDED.price,
      synced_at  = now()
RETURNING sku, qty, price, (xmax = 0) AS is_insert;
text
   sku    | qty | price  | is_insert
----------+-----+--------+-----------
 SKU-1001 | 150 | 339.00 | f
 SKU-1002 | 350 | 129.00 | f
 SKU-1004 |  45 | 259.00 | t
(3 rows)

INSERT 0 3
sql
-- 步骤 4：第三次同步，价格完全没变 —— 用 WHERE 抑制无意义写入
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘',   0, 339.00),
  ('SKU-1002', '无线鼠标',   0, 129.00),
  ('SKU-1004', '显示器支架', 0, 259.00)
ON CONFLICT (sku) DO UPDATE
  SET price     = EXCLUDED.price,
      synced_at = now()
WHERE inventory.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price;
text
 sku | price
-----+-------
(0 rows)

INSERT 0 0
-- 三行价格都没变，因此没有任何行被真正 UPDATE，
-- 不产生死元组，也不写 WAL —— 这正是我们想要的效果
sql
-- 步骤 5：核对最终状态
SELECT sku, qty, price FROM inventory ORDER BY sku;
text
   sku    | qty | price
----------+-----+--------
 SKU-1001 | 150 | 339.00
 SKU-1002 | 350 | 129.00
 SKU-1003 |  80 | 199.00
 SKU-1004 |  45 | 259.00
(4 rows)
sql
-- 步骤 6：条件 UPSERT —— 只在"新价格更低"时才覆盖（防止上游误提价）
INSERT INTO inventory (sku, name, qty, price) VALUES
  ('SKU-1001', '机械键盘', 0, 999.00)
ON CONFLICT (sku) DO UPDATE
  SET price     = EXCLUDED.price,
      synced_at = now()
WHERE EXCLUDED.price < inventory.price      -- 旧值在左、新值在右都可以，语义要看清楚
RETURNING sku, price;
text
 sku | price
-----+-------
(0 rows)

INSERT 0 0
-- 新价格 999.00 大于旧价 339.00，条件不成立，未更新
sql
-- 步骤 7：同步日志表 —— 每次都记录这次是新增还是更新
DROP TABLE IF EXISTS sync_log;
CREATE TABLE sync_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  batch_no    text        NOT NULL,
  sku         text        NOT NULL,
  action      text        NOT NULL,
  price       numeric(12,2) NOT NULL,
  logged_at   timestamptz NOT NULL DEFAULT now()
);

WITH upserted AS (
  INSERT INTO inventory (sku, name, qty, price) VALUES
    ('SKU-1002', '无线鼠标', 10, 119.00),
    ('SKU-1005', 'HDMI 线缆', 500, 39.00)
  ON CONFLICT (sku) DO UPDATE
    SET price = EXCLUDED.price,
        synced_at = now()
  RETURNING sku, price, (xmax = 0) AS is_insert
)
INSERT INTO sync_log (batch_no, sku, action, price)
SELECT 'BATCH-20260924', sku, CASE WHEN is_insert THEN 'INSERT' ELSE 'UPDATE' END, price
FROM upserted
RETURNING batch_no, sku, action, price;
text
    batch_no    |   sku    | action | price
----------------+----------+--------+--------
 BATCH-20260924 | SKU-1002 | UPDATE | 119.00
 BATCH-20260924 | SKU-1005 | INSERT |  39.00
(2 rows)

INSERT 0 2

上面这条语句展示了 UPSERT 的常用组合拳：RETURNING (xmax = 0) 区分动作 → CTE 把结果喂给日志表，一次往返完成"同步 + 审计"，全程在数据库内部完成，无应用层往返。

注意事项 ​

  • DO UPDATE 必须带冲突目标。 只写 ON CONFLICT DO UPDATE 会报错。DO NOTHING 才可以省略括号（表示匹配任意唯一约束）。
sql
-- 错误！
INSERT INTO inventory (sku, name, qty, price)
VALUES ('SKU-1001', 'x', 1, 1.00)
ON CONFLICT DO UPDATE SET qty = EXCLUDED.qty;
-- ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
  • 冲突目标必须有唯一索引支撑。 冲突目标里的列（或表达式）必须能唯一确定一个 UNIQUE / PRIMARY KEY / EXCLUDE 约束或唯一索引。给普通列写 ON CONFLICT 一定报错。
sql
-- 错误！qty 上没有唯一索引
INSERT INTO inventory (sku, name, qty, price)
VALUES ('SKU-1001', 'x', 1, 1.00)
ON CONFLICT (qty) DO NOTHING;
-- ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
  • EXCLUDED 是新值，不是旧值。 混淆这两者是 UPSERT 最经典的 bug。EXCLUDED.col = 本次候选行的值；table_name.col 或裸列名 = 表中现有值。
sql
-- 意图是"累加"，实际效果是"覆盖为新值"
SET qty = EXCLUDED.qty;             -- 覆盖（旧值丢失）
SET qty = inventory.qty + EXCLUDED.qty;   -- 累加（正确）

  • DO NOTHING 与 DO UPDATE ... WHERE 都可能导致影响行数为 0。 不要把 INSERT 0 N 当作"提交了 N 行"的确认。

  • 命中冲突也会消耗序列号。 IDENTITY / serial 主键出现空洞是 UPSERT 的正常表现。高冲突率场景下空洞会明显放大，需要业务侧接受。

  • 高并发下可能报序列化失败。 REPEATABLE READ / SERIALIZABLE 下两个事务同时更新同一冲突行会抛 could not serialize access due to concurrent update，应用层必须实现重试。

  • UPSERT 无法处理"冲突键位于不同唯一约束"的情况。 若一行同时违反两个不同的唯一约束，ON CONFLICT (a) 只处理 a 对应的冲突，b 的冲突仍会直接报错。

  • ON CONFLICT 不能与 MERGE 混用，也不要指望用它替代复杂的多表合并逻辑——那种场景 MERGE 更合适。

  • 部分唯一索引必须带谓词。 冲突目标写成 (col1, col2) WHERE ... 且谓词要与索引定义一致，否则无法匹配，报 no unique or exclusion constraint matching。

最佳实践 ​

  1. 同步类写入一律用 UPSERT，不要"先查后写"。 后者在并发下存在竞态窗口，且需要两倍往返。
sql
-- 不推荐：两条语句之间存在竞态
SELECT 1 FROM inventory WHERE sku = 'SKU-1001';
-- 若不存在则 INSERT，否则 UPDATE

-- 推荐：一条语句，原子完成
INSERT INTO inventory (sku, name, qty, price) VALUES ('SKU-1001', '机械键盘', 10, 339.00)
ON CONFLICT (sku) DO UPDATE
  SET qty = inventory.qty + EXCLUDED.qty,
      price = EXCLUDED.price,
      synced_at = now();
  1. 明确区分"覆盖"与"累加"。 覆盖写 EXCLUDED.col，累加写 table.col + EXCLUDED.col，一次同步里两者常常并存，务必逐列确认语义。
sql
ON CONFLICT (sku) DO UPDATE
  SET price = EXCLUDED.price,                    -- 价格：覆盖
      qty   = inventory.qty + EXCLUDED.qty;      -- 库存：累加
  1. 用 WHERE ... IS DISTINCT FROM ... 抑制无意义写入。 定时同步任务每轮都会全量提交一遍，若不加以过滤，会造成大量死元组与 WAL。
sql
ON CONFLICT (sku) DO UPDATE
  SET price = EXCLUDED.price, updated_at = now()
WHERE inventory.price IS DISTINCT FROM EXCLUDED.price
   OR inventory.name  IS DISTINCT FROM EXCLUDED.name;
  1. 用 RETURNING (xmax = 0) 统计新增与更新。 一次往返就能拿到"新增多少、更新多少"，无需二次查询。
sql
INSERT INTO inventory (sku, name, qty, price) VALUES (...)
ON CONFLICT (sku) DO UPDATE SET ...
RETURNING (xmax = 0) AS is_insert;
  1. 把 UPSERT 的结果直接接进 CTE 做审计。 同步 + 打日志 + 统计，一条语句搞定。
sql
WITH u AS (
  INSERT INTO inventory (sku, name, qty, price) VALUES (...)
  ON CONFLICT (sku) DO UPDATE SET ...
  RETURNING sku, price, (xmax = 0) AS is_insert
)
INSERT INTO sync_log (sku, action, price)
SELECT sku, CASE WHEN is_insert THEN 'INSERT' ELSE 'UPDATE' END, price FROM u;

  1. 应用层把序列化错误当作可重试异常。 捕获 SQLSTATE 40001 后重试整个事务（建议最多 3 次并加退避），而不是直接报错给用户。

  2. 高冲突率场景考虑改造方案。 若一次同步中 90% 都是更新，可先用一条 UPDATE ... FROM 处理既有行、再 INSERT 剩余新行，比逐个 UPSERT 更省（但仍需处理并发）。是否需要改造要以实测为准。

  3. 为冲突目标列建立唯一索引，并保持列顺序一致。 复合冲突目标的列顺序应与唯一索引的列顺序一致，才能命中索引推断。

sql
-- 索引列顺序与冲突目标顺序一致
CREATE UNIQUE INDEX idx_daily_stats_date_channel ON daily_stats (stat_date, channel);
INSERT INTO daily_stats (stat_date, channel, pv, uv) VALUES (...)
ON CONFLICT (stat_date, channel) DO UPDATE SET pv = EXCLUDED.pv;

下一节

继续学习：更新数据（UPDATE）

参考链接 ​