PostgreSQL如何复用bigserial将staging表数据插入两张关联表

你的现有写法对插入main表返回的id集合和stage表做了笛卡尔积，每一个生成的main表主键都会和所有stage表的行匹配，最终会生成 N*N 条冗余的secondary表记录，所以不符合预期。

方案1：字段匹配（适合stage表(a1,b2,c3)组合唯一的场景） #

插入main表时同时返回插入的业务字段，用这些字段和stage表做等值匹配，保证一一对应：

WITH tmp AS (
  INSERT INTO main (a1, b2, c3)
  SELECT a1, b2, c3 FROM stage
  -- 插入时返回主键+关联用的业务字段
  RETURNING id, a1, b2, c3
)
INSERT INTO secondary (mainid, d4, e5)
SELECT 
  tmp.id,
  s.d4,
  s.e5
FROM tmp
-- 用业务字段关联stage表，实现一一对应
JOIN stage s 
  ON tmp.a1 = s.a1 
  AND tmp.b2 = s.b2 
  AND tmp.c3 = s.c3;

方案2：行号匹配（全场景通用，支持字段重复的情况） #

给stage的行和插入生成的id分别添加序号，按序号一一对应，不受字段重复影响：

WITH numbered_stage AS (
  -- 给stage的每一行加唯一序号
  SELECT *, ROW_NUMBER() OVER () AS rn FROM stage
),
tmp AS (
  INSERT INTO main (a1, b2, c3)
  -- 按序号顺序插入，保证返回的id顺序和stage行顺序一致
  SELECT a1, b2, c3 FROM numbered_stage ORDER BY rn
  RETURNING id
),
numbered_main AS (
  -- 给返回的主键也按顺序加序号
  SELECT id, ROW_NUMBER() OVER () AS rn FROM tmp
)
INSERT INTO secondary (mainid, d4, e5)
SELECT 
  nm.id,
  ns.d4,
  ns.e5
FROM numbered_main nm
-- 按序号关联，实现行级一一对应
JOIN numbered_stage ns ON nm.rn = ns.rn;

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