PostgreSQL中如何将插入生成的ID与源数据关联实现多表插入？

要解决这个问题，核心是把源数据new_dividends里的effect字段和新生成的transaction_id准确对应，下面提供两种实用方案：

方案一：利用源数据唯一字段组合关联（优先选用） #

如果new_dividends中的transaction_date和mutation组合能唯一标识每一行，直接通过这两个字段关联插入结果和源数据即可：

WITH inserted_transactions AS (
    INSERT INTO transaction (transaction_date, mutation)
    SELECT transaction_date, mutation FROM new_dividends
    RETURNING transaction_id, transaction_date, mutation
)
INSERT INTO dividend (transaction_id, effect)
SELECT it.transaction_id, nd.effect
FROM inserted_transactions it
JOIN new_dividends nd 
    ON it.transaction_date = nd.transaction_date 
    AND it.mutation = nd.mutation;

方案二：生成临时唯一标识关联（通用方案） #

如果源数据没有唯一字段组合，给每一行生成临时序号，通过序号确保transaction_id和effect的对应关系：

WITH numbered_source AS (
    -- 给源数据生成临时唯一序号，同时指定排序规则保证顺序稳定
    SELECT 
        transaction_date, 
        mutation, 
        effect,
        row_number() OVER (ORDER BY transaction_date, mutation) AS temp_row_id
    FROM new_dividends
),
inserted_transactions AS (
    INSERT INTO transaction (transaction_date, mutation)
    SELECT transaction_date, mutation FROM numbered_source
    -- 给插入的结果也生成对应序号（依赖transaction_id自增特性，和插入顺序一致）
    RETURNING transaction_id,
           row_number() OVER (ORDER BY transaction_id) AS temp_row_id
)
INSERT INTO dividend (transaction_id, effect)
SELECT it.transaction_id, ns.effect
FROM inserted_transactions it
JOIN numbered_source ns ON it.temp_row_id = ns.temp_row_id;

注意事项 #

• 方案二中必须给row_number()指定ORDER BY，否则插入顺序可能不稳定，导致序号对应错误。
• 如果transaction_id不是自增序列（比如用UUID），可以改用ctid（物理行号）生成临时序号，但ctid会因表 vacuum 变化，仅适合一次性插入场景。

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