如何用PostgreSQL的WITH子句实现单查询多表关联插入

嘿，这个场景我太熟悉了！之前我也踩过你那个“未被引用的CTE不执行”的坑，先给你划个重点：PostgreSQL里，WITH子句中未被主查询引用的DML语句（INSERT/UPDATE/DELETE）是不会执行的——所以你之前写的inserted2那个CTE其实根本没跑，whatever2里根本没插入数据，这可不是写法优雅的问题，是功能失效的问题！

正确的单条SQL写法 #

我们可以利用PostgreSQL支持在WITH中包含多个DML语句的特性，让两个关联表的插入都基于主表返回的ID，同时确保所有操作都被执行：

WITH main_insert AS (
  -- 第一步：插入主表，返回生成的ID
  INSERT INTO whatever1 (whatever)
  VALUES ('whatever')
  RETURNING id
),
insert_wh2 AS (
  -- 第二步：插入第一个关联表，用主表返回的ID
  INSERT INTO whatever2 (id, whatever)
  SELECT id, 'whatever' FROM main_insert
  RETURNING 1 -- 返回一个占位值，方便主查询引用
),
insert_wh3 AS (
  -- 第三步：插入第二个关联表，同样用主表的ID
  INSERT INTO whatever3 (id, whatever)
  SELECT id, 'whatever' FROM main_insert
  RETURNING 1
)
-- 主查询必须引用所有DML CTE，确保它们都执行，同时可以返回主表ID作为反馈
SELECT main_insert.id FROM main_insert, insert_wh2, insert_wh3;

为什么这个写法能工作？ #

1. main_insert负责插入主表并返回ID，后面两个关联表的插入都直接引用它的结果，不需要依赖彼此；
2. insert_wh2和insert_wh3都加了RETURNING 1，这样主查询可以通过FROM子句引用它们，强制PostgreSQL执行这两个插入操作；
3. 整个逻辑是原子性的——要么所有插入都成功，要么全部回滚，符合事务一致性要求。

更简洁的变体（如果不需要返回结果） #

如果你不需要返回主表的ID，只想执行插入操作，可以把主查询改成：

WITH main_insert AS (
  INSERT INTO whatever1 (whatever)
  VALUES ('whatever')
  RETURNING id
),
insert_wh2 AS (
  INSERT INTO whatever2 (id, whatever)
  SELECT id, 'whatever' FROM main_insert
),
insert_wh3 AS (
  INSERT INTO whatever3 (id, whatever)
  SELECT id, 'whatever' FROM main_insert
)
SELECT 1 FROM main_insert, insert_wh2, insert_wh3;

这样同样能确保三个插入操作都被执行，而且输出一个简单的1作为执行成功的标识。

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