PostgreSQL中INSERT RETURNING引用未插入列的解决方法

背景 #

• 数据库现有三张表：Record、Expense、Revenue
• Expense和Revenue表的record_ptr_id字段是关联Record表id字段的外键
• 各表字段：
  • Record：id、amount、record_date、property_id
  • Expense：record_ptr_id、expense_type
  • Revenue：record_ptr_id、revenue_type
• 需求：将Expense_temp表中的2条新费用记录，先插入Record表，再插入Expense表，完成关联更新

错误尝试的SQL #

以下SQL尝试在RETURNING子句中获取expense_type但失败：

WITH inserted_records AS (
INSERT INTO record(amount, record_date, property_id)
SELECT e.amount, e.record_date, e.property_id 
FROM expense_tmp e 
RETURNING id, /* 此处无法生效 */ e.expense_type
)
INSERT INTO expense(record_prt_id, expense_type) 
SELECT r.id, r.expense_type
FROM inserted_records r;  

错误提示 #

ERROR: missing FROM-clause entry for table "e"
LINE 5: RETURNING id, e.expense_type

可行解决方案（由@mwalter提供） #

通过预先生成对应ID并关联临时表数据，完成跨表关联插入：

CREATE SEQUENCE expense_tmp_seq;

SELECT setval('expense_tmp_seq', (SELECT currval('record_seq')));

WITH expense_tmp_with_id AS (
    SELECT nextval('expense_tmp_seq') AS id, record_date, expense_type, property_id
    FROM expense_tmp
),
inserted_records AS (
    INSERT INTO record(id, amount, record_date, property_id)
    SELECT nextval('record_seq'), e.amount, e.record_date, e.property_id 
    FROM expense_tmp e 
    RETURNING id
)
INSERT INTO expense(record_ptr_id, expense_type) 
SELECT s.id, s.expense_type
FROM inserted_records r INNER JOIN expense_tmp_with_id s ON r.id = s.id;

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