PostgreSQL中基于前序插入返回值实现多表关联插入的问题

错误原因分析 #

第一个写法的问题 #

• 语法错误：列名不能用单引号包裹，你写的'other columns'是字符串常量，不是合法列名。如果列名包含空格或特殊字符，需用双引号（比如"other columns"），否则直接写列名即可。
• 可写CTE重复读取：包含INSERT/UPDATE/DELETE的可写CTE，其结果集只能被后续查询读取一次。你在second_insert的VALUES中多次用子查询select id from first_insert，属于重复读取可写CTE，会触发报错。

第二个写法的问题 #

• 逻辑错误：third_insert中引用second_insert返回的ID，而这个ID是table_2的主键，不是你需要的table_1的ID，导致table_2的第二条插入数据使用了错误的关联值。
• 同样存在列名单引号的语法错误。

最优单查询实现方案 #

利用INSERT ... SELECT语法代替VALUES，可以一次性复用table_1返回的ID插入多条数据到table_2，同时避免重复读取可写CTE的问题：

with first_insert as (
  insert into table_1 (user_1, user_2)
  values (${user_1}, ${user_2})
  returning id as table1_id
),
second_insert as (
  -- 向table_2插入2条关联数据，用SELECT复用table1_id
  insert into table_2 (some_id, other_columns)
  select table1_id, 'stuff'
  from first_insert
  -- 用cross join generate_series快速生成指定行数的重复数据
  cross join generate_series(1, 2)
  returning id as table2_id
)
-- 向table_3插入关联数据，直接引用first_insert的table1_id
insert into table_3 (some_id, other_columns)
select table1_id, 'stuff'
from first_insert;

如果不想用generate_series，也可以用UNION ALL手动构造多行数据：

with first_insert as (
  insert into table_1 (user_1, user_2)
  values (${user_1}, ${user_2})
  returning id as table1_id
),
second_insert as (
  insert into table_2 (some_id, other_columns)
  select table1_id, 'stuff' from first_insert
  union all
  select table1_id, 'stuff' from first_insert
  returning id as table2_id
)
insert into table_3 (some_id, other_columns)
select table1_id, 'stuff'
from first_insert;

关键说明 #

• 可写CTE的结果集可以被后续的CTE或主查询读取，但只能读取一次，用INSERT ... SELECT可以一次性完成多条插入，无需重复引用。
• 始终确保列名的写法正确：不要用单引号包裹列名，特殊列名用双引号。

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