从单查询向多表插入：拆分questions表至campaigns与campaign_actions

安全阿岳

2026-7-30

解决questions表拆分到campaigns和campaign_actions的迁移问题

你不需要给campaigns表添加临时字段，用PostgreSQL的CTE（公共表表达式）就能一次性完成关联插入，核心是在插入campaigns时把原questions表的关联字段一起带入，再通过RETURNING拿到新生成的campaign_id和原问题的关联信息。

正确的迁移SQL

WITH inserted_campaigns AS (
  INSERT INTO "campaigns" (name, tenant_id)
  SELECT 
    q.title AS name,
    q.tenant_id AS tenant_id
  FROM "questions" AS q
  -- 关键：把原表需要关联的字段包含在SELECT里，才能在RETURNING中带出
  RETURNING 
    id AS campaign_id,
    q.id AS question_id,
    q.app_area_id AS app_area_id,
    q.tenant_id AS tenant_id
)
INSERT INTO "campaign_actions" (campaign_id, question_id, app_area_id, tenant_id)
SELECT 
  campaign_id,
  question_id,
  app_area_id,
  tenant_id
FROM inserted_campaigns;

原代码的问题点

RETURNING不能用into关键字：into是单行结果赋值的语法，CTE的RETURNING只需定义字段别名即可。
RETURNING无法直接引用原表字段：必须在INSERT的SELECT语句中包含原表的q.id和q.app_area_id，才能在RETURNING中返回这些值，否则PostgreSQL无法识别原表字段。