如何通过WITH CTE在PostgreSQL中批量向多表插入关联数据并获取ID？

本文共计1114个文字，预计阅读时间需要5分钟。

PostgreSQL 中的 `INSERT ... RETURNING` 可以在插入后立即获取生成的主键（例如 `id`），而 `WITH`（子查询或CTE）可以将它暂存下来，供后续的 `INSERT` 使用。这不是事务封装，而是真正的SQL语句，它在一个数据库连接和事务上下文中完成多表关联写入。

典型场景：插入一条用户记录，同时往 profile 和 user_settings 表里写对应配置，且依赖刚生成的 users.id。

• 必须用 WITH 定义第一个 CTE，并在其内部使用 INSERT ... RETURNING id
• 后续 CTE 或主查询中可通过 SELECT 引用前一个 CTE 的结果，但不能跨 CTE 写入（即不能在第二个 CTE 里再 INSERT 并 RETURNING 到第三个）
• 所有 INSERT 必须是同一层级或嵌套在 SELECT 中（例如用 INSERT INTO ... SELECT FROM ...）

为什么不能直接在第二个 INSERT 里用 RETURNING 再传 ID

因为 PostgreSQL 不允许在非最外层 INSERT 中使用 RETURNING——你可以在 CTE 里 INSERT 并 RETURNING，也可以在主查询 INSERT 并 RETURNING，但不能在 CTE 的 CTE 里再 RETURNING。常见错误是这样写：

WITH u AS (INSERT INTO users (name) VALUES ('alice') RETURNING id), p AS (INSERT INTO profile (user_id, bio) VALUES (u.id, 'hello') RETURNING id) SELECT * FROM p;

这会报错：ERROR: INSERT has more than one RETURNING list。PostgreSQL 要求整个语句最多一个顶层 RETURNING（主查询的），CTE 内部的 RETURNING 只能用于传递值，不能“再导出”。

• CTE 中的 INSERT ... RETURNING 是合法的，但它返回的结果只能被其他 CTE 或主查询的 SELECT / INSERT ... SELECT 消费
• 想把多个表的插入结果都拿回来？只能靠主查询的 RETURNING 统一收口，或者拆成多个语句
• 如果某张表插入失败（如外键约束），整个 WITH 语句会回滚，这是原子性保障，但也意味着无法“跳过失败项”

实际可运行的多表插入写法（带外键依赖）

以下例子插入 users → profiles → notifications，三级依赖，全部在一条语句中完成：

WITH inserted_user AS ( INSERT INTO users (name, email) VALUES ('bob', 'bob@example.com') RETURNING id ), inserted_profile AS ( INSERT INTO profiles (user_id, avatar_url) SELECT id, 'https://example.com/avatar/bob' FROM inserted_user RETURNING user_id, id AS profile_id ) INSERT INTO notifications (user_id, channel, enabled) SELECT user_id, 'email', true FROM inserted_profile RETURNING user_id, 'done'::text AS status;

关键点：

• inserted_user 提供原始 id，供下一层插入引用
• inserted_profile 用 SELECT FROM inserted_user 实现关联，不能写 VALUES (inserted_user.id, ...) —— CTE 名不是变量，不能直接点属性
• 最后一层必须是主 INSERT（不能是 CTE），才能用 RETURNING 输出最终结果
• 所有表名、字段名、值类型必须严格匹配，否则在预处理阶段就报错

容易忽略的兼容性和性能坑

这种写法在 PostgreSQL 9.1+ 支持，但不同版本对 CTE 的优化策略不同：9.6 之前 CTE 默认物化（强制执行完再进下一步），可能影响大表插入性能；从 12 开始支持 MATERIALIZED / NOT MATERIALIZED 显式控制，但多数场景无需干预。

• 如果插入量大（比如批量导入 1000 行用户+关联数据），别硬套单条 WITH —— 改用临时表 + 多条 INSERT 更清晰、易调试
• CTE 中的 INSERT 不支持 ON CONFLICT 的 RETURNING 组合（某些旧版会静默忽略 RETURNING），测试时务必验证冲突路径是否真能返回预期值
• 不要在 CTE 里插入多行后只取一行（如 LIMIT 1），除非业务逻辑明确允许丢弃；否则应确保输入集和输出集一一对应

最麻烦的往往不是语法，而是当某个中间表有触发器或复杂 CHECK 约束时，错误信息指向的是 CTE 名而非具体表，排查得翻着看每一层的字段定义和约束条件。