Postgres批量插入Action后创建Person_Action关联的实现问询

要实现批量创建Action行、返回ID并准确关联到对应Person的需求，核心是绑定原始输入数据和插入结果的对应关系，避免因返回顺序不确定导致的匹配错误。以下是无需在Action表添加冗余字段的实现方式：

实现思路 #

通过WITH子句构造带有序号的输入数据集，利用序号绑定每个Action名称和对应的Person ID；插入Action时按序号排序保证顺序，再通过序号关联插入后的Action ID与原始Person ID，最终批量插入Person_Action关联表。

具体SQL代码 #

WITH input_actions AS (
  -- 构造带序号的输入数据，替换为你的批量输入内容
  SELECT 
    action_info.name,
    person_info.person_id,
    action_info.idx
  FROM unnest(ARRAY['made soup', 'ate soup']) WITH ORDINALITY AS action_info(name, idx)
  -- 通过序号绑定Action和对应的Person ID
  JOIN unnest(ARRAY[1, 2]) WITH ORDINALITY AS person_info(person_id, idx) 
    ON action_info.idx = person_info.idx
), inserted_actions AS (
  -- 批量插入Action，按序号排序确保插入顺序与输入一致
  INSERT INTO Action (name)
  SELECT name FROM input_actions ORDER BY idx
  RETURNING id AS action_id
)
-- 按序号关联，将Action ID与Person ID批量插入关联表
INSERT INTO Person_Action (person_id, action_id)
SELECT 
  input_actions.person_id,
  inserted_actions.action_id
FROM input_actions
JOIN (
  -- 给插入的Action ID按生成顺序加序号，与输入的序号匹配
  SELECT action_id, ROW_NUMBER() OVER (ORDER BY action_id) AS idx
  FROM inserted_actions
) AS numbered_actions ON input_actions.idx = numbered_actions.idx;

关键说明 #

1. 序号绑定：使用WITH ORDINALITY给每个输入行添加唯一序号，确保Action名称和Person ID的对应关系不会错乱。
2. 顺序保证：插入Action时通过ORDER BY idx强制按输入顺序插入，结合ROW_NUMBER()给返回的Action ID添加对应序号，彻底解决返回顺序不确定的问题。
3. 无冗余字段：全程不需要在Action表中添加person_id字段，保持表结构的合理性。

替代简化方案（适用于Action名称唯一的场景） #

如果你的Action名称不会重复，可以省去序号关联，直接通过名称匹配：

WITH input_actions AS (
  SELECT 'made soup' AS name, 1 AS person_id
  UNION ALL
  SELECT 'ate soup' AS name, 2 AS person_id
), inserted_actions AS (
  INSERT INTO Action (name)
  SELECT name FROM input_actions
  RETURNING id AS action_id, name
)
INSERT INTO Person_Action (person_id, action_id)
SELECT ia.person_id, ins.action_id
FROM input_actions ia
JOIN inserted_actions ins ON ia.name = ins.name;

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