PostgreSQL从单张导入表向一对多关联表批量插入数据的实现问题

针对无法直接匹配插入生成的thing_id和原始guid的问题，以下提供两种可落地的实现方案：

方案1：生产环境稳妥方案（支持并发，无顺序依赖） #

该方案通过临时增加映射列的方式直接关联源数据和插入后生成的主键，100%保证数据准确性，不受其他并发写入操作影响：

-- 1. 给thing表新增临时映射列，仅存储导入过程的关联ID，属于元数据操作，执行速度极快
ALTER TABLE thing ADD COLUMN temp_import_source_id INT;

-- 2. 批量插入thing表并关联插入thing_identifier表
WITH inserted_thing AS (
  INSERT INTO thing (thing_name, thing_attribute, temp_import_source_id)
  SELECT thing_name, thing_attribute, id
  FROM things_to_add
  RETURNING thing_id, temp_import_source_id
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT 
  it.thing_id,
  tta.guid
FROM inserted_thing it
INNER JOIN things_to_add tta ON it.temp_import_source_id = tta.id;

-- 3. 删除临时映射列，恢复thing表原有结构
ALTER TABLE thing DROP COLUMN temp_import_source_id;

方案2：轻量方案（无表结构修改，仅适合独占导入场景） #

如果导入操作是独占执行（无其他并发写入thing表的任务），可以利用插入顺序和返回主键顺序一致的特性，通过数组映射实现关联，无需修改表结构：

WITH inserted_thing AS (
  -- 按things_to_add的主键顺序插入，保证返回的thing_id顺序和源表顺序完全对应
  INSERT INTO thing (thing_name, thing_attribute)
  SELECT thing_name, thing_attribute
  FROM things_to_add
  ORDER BY id ASC
  RETURNING thing_id
),
id_map AS (
  -- 将生成的thing_id按顺序转为数组
  SELECT array_agg(thing_id) AS thing_id_arr
  FROM inserted_thing
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT
  thing_id_arr[rn],
  guid
FROM (
  -- 给源表按主键顺序生成行号，和数组下标一一对应
  SELECT 
    guid,
    row_number() OVER(ORDER BY id ASC) AS rn
  FROM things_to_add
) t
CROSS JOIN id_map;

方案选型建议 #

• 生产环境优先选择方案1，虽然需要临时修改表结构，但PG的增删列操作为元数据修改，不会扫描全表，执行效率极高，且完全避免并发、顺序错乱导致的数据关联错误。
• 测试环境、小数据量导入、且确认无其他写入操作时，可以选择方案2，执行步骤更简单。

内容的提问来源于stack exchange，提问作者M. Andersen