PostgreSQL多表（含关联表）批量插入数据问题求助

错误原因解析 #

第一种CTE写法错误 #

• 第一个CTE的INSERT语句中，SELECT "source"."foo"未指定FROM "source"，导致PostgreSQL无法识别source表，触发ERROR: missing FROM-clause entry for table "source"。
• 即使补上FROM "source"，第二个CTE也无法建立source与insert_destination的关联——因为insert_destination仅返回destination.id，没有对应的source.id，无法保证每个source行对应正确的destination行。

第二种CTE写法错误 #

PostgreSQL不支持在SELECT子句中嵌套INSERT语句，这种语法不符合SQL标准，因此触发ERROR: syntax error at or near "INTO"。

正确实现方案 #

方案1：使用LATERAL子查询（推荐，适用于所有场景） #

该方案通过LATERAL为每个source行单独插入对应的destination记录，确保source.id与destination.id一一对应，即使foo字段存在重复值也能正常工作：

WITH insert_pairs AS (
  SELECT s.id AS source_id, d.id AS destination_id
  FROM "source" s
  LATERAL (
    INSERT INTO "destination" ("foo")
    VALUES (s."foo")
    RETURNING "id"
  ) d
)
INSERT INTO "source_destination" ("source_id", "destination_id")
SELECT source_id, destination_id FROM insert_pairs
RETURNING COUNT(*);

方案2：基于foo字段关联（仅适用于foo字段唯一的场景） #

如果source.foo字段是唯一的，可以通过JOIN关联插入后的destination记录与原source记录：

WITH inserted_destinations AS (
  INSERT INTO "destination" ("foo")
  SELECT "foo" FROM "source"
  RETURNING "id" AS destination_id, "foo"
)
INSERT INTO "source_destination" ("source_id", "destination_id")
SELECT s.id, id.destination_id
FROM "source" s
JOIN inserted_destinations id ON s."foo" = id."foo"
RETURNING COUNT(*);

原理说明 #

• 方案1中的LATERAL子查询会遍历source表的每一行，执行一次INSERT操作生成对应的destination记录，并返回新生成的destination.id，从而直接得到source.id与destination.id的配对关系。
• 方案2先批量插入所有foo值到destination，再通过foo字段关联原source记录，仅当foo唯一时能保证关联的准确性。

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