如何在PostgreSQL的多个插入请求中使用RETURNING返回的ID值

报错原因 #

PostgreSQL 的WITH公共表达式仅对紧随其后的第一条DML语句可见，你写的第二条INSERT语句已经脱离了WITH的作用域，因此会提示rows关系不存在。

解决方案 #

方案1：链式CTE单语句实现（推荐，原生支持原子性） #

将所有插入操作都封装到WITH的CTE链中，所有后续CTE都可以访问最开始返回的主表ID，整个操作要么全部成功要么全部回滚，不需要手动控制事务：

WITH inserted_contact AS (
  -- 第一步插入主表，返回ID
  INSERT INTO "Contact"(name, gender, city, birthdate)
  VALUES ('Name', 1, 'City', '2000-02-03')
  RETURNING id
),
inserted_education AS (
  -- 第二步插入教育表，支持一次性插多条
  INSERT INTO "Education"(user_id, place, degree, endyear)
  SELECT id, 'some_place', 'some_state', 1990 FROM inserted_contact
  UNION ALL -- 用UNION ALL拼接多条同表插入数据
  SELECT id, 'another_school', 'master', 2014 FROM inserted_contact
),
inserted_status AS (
  -- 第三步插入状态表，也支持插多条
  INSERT INTO "Status"(user_id, status)
  SELECT id, 'val' FROM inserted_contact
  UNION ALL
  SELECT id, 'another_status' FROM inserted_contact
)
-- 最后可按需返回插入的主表ID，也可以返回其他你需要的字段
SELECT id FROM inserted_contact;

方案2：事务+临时表存储ID（适合多步复杂业务场景） #

如果你的业务还有其他依赖主表ID的后续操作，可以用事务配合临时表存储生成的ID，在整个事务生命周期内都可以调用：

BEGIN;
-- 创建临时表存储主表ID，事务提交后自动删除
CREATE TEMP TABLE temp_contact_id (id INT) ON COMMIT DROP;

-- 插入主表，将ID存入临时表
INSERT INTO "Contact"(name, gender, city, birthdate)
VALUES ('Name', 1, 'City', '2000-02-03')
RETURNING id INTO temp_contact_id;

-- 随便插多少条教育记录都可以
INSERT INTO "Education"(user_id, place, degree, endyear)
VALUES
((SELECT id FROM temp_contact_id), 'some_place', 'some_state', 1990),
((SELECT id FROM temp_contact_id), 'another_school', 'master', 2014);

-- 随便插多少条状态记录都可以
INSERT INTO "Status"(user_id, status)
VALUES
((SELECT id FROM temp_contact_id), 'val'),
((SELECT id FROM temp_contact_id), 'another_status');

COMMIT;

内容的提问来源于stack exchange，提问作者Jean Panov