Postgres批量插入ON CONFLICT时更新字段并写入price_history的实现方案

PostgreSQL的ON CONFLICT子句本身不支持直接执行跨表操作，你可以通过以下三种常用方案实现需求：

方案1：使用UPDATE触发器（最推荐，可复用性最高） #

给items表创建更新触发器，只要price字段发生变更就自动写入price_history表，和你现有的ON CONFLICT逻辑完全兼容，不需要修改现有插入代码。

步骤1：创建触发器函数 #

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
  -- 只有price字段发生变化时才写入历史表，按需调整字段匹配规则
  IF OLD.price <> NEW.price THEN
    INSERT INTO price_history (item_id, old_price, new_price, change_time)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到items表 #

CREATE TRIGGER trigger_item_price_change
AFTER UPDATE OF price ON items
FOR EACH ROW EXECUTE FUNCTION log_price_change();

配置完成后，你原有的INSERT ... ON CONFLICT DO UPDATE语句更新price字段时，会自动触发历史数据插入，无需修改现有插入逻辑。 #

方案2：单语句可写CTE（无需修改表结构） #

如果不想新增触发器，可以把插入更新、历史写入合并为同一个SQL语句，用CTE链式执行：

const query = format(`
WITH inserted_items AS (
  INSERT INTO items (site_id, store_id, title, price, quantity, item_code, package_quantity, unit_adjusted_price)
  VALUES %L
  ON CONFLICT (你的冲突约束字段, 比如item_code, store_id)
  DO UPDATE SET price = EXCLUDED.price
  -- 返回所有插入/更新成功的条目，以及价格变动标记
  RETURNING id, price, CASE WHEN xmax = 0 THEN false ELSE true END AS is_updated
)
INSERT INTO price_history (item_id, new_price, record_time)
SELECT id, price, NOW()
FROM inserted_items
WHERE is_updated = true -- 只筛选冲突更新的条目写入历史
RETURNING item_id;
`, items);

注意：把你的冲突约束字段替换为你实际用来判断冲突的唯一键字段，xmax = 0是PostgreSQL内置规则，用来区分该行是新插入还是触发了更新。

方案3：应用层事务拆分 #

你也可以在应用层开启事务，分两步执行：

1. 执行带ON CONFLICT DO UPDATE的批量插入，用RETURNING子句返回所有更新成功的条目ID和新价格
2. 拿着返回的结果集执行批量插入到price_history的操作
3. 提交事务
   这个方案逻辑最直观，但需要额外编写应用层代码处理结果集，性能略低于前两种数据库端实现。

内容的提问来源于stack exchange，提问作者TheUnreal