Postgres joining data onto inserted generated IDs

Question

In postgres, I want to insert multiple rows into a first table, and then use the generated identities to insert multiple rows into a second table. Is there a way to join the returning IDs with my data?

I have the following code snippet, in which I first insert new dividends into the inserted table, and then use those generated IDs to insert into the dividend table.

WITH inserted AS (
    INSERT INTO transaction (transaction_date, mutation)
    SELECT transaction_date, mutation FROM new_dividends
    RETURNING transaction_id
)
INSERT INTO dividend (transaction_id, effect)
SELECT transaction_id, effect FROM inserted;

This does not work, because in the second insert, the effect should be selected from new_dividends, as it is not present in the returning statement.

EDIT: The transaction_id gets generated in transaction, so it is not selected from new_dividends. Thus there is no common unique column between the two resultsets.

Answer 1

The fact the you are selecting from a CTE doesn't stop you from using any join you want: demo:

WITH inserted 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 transaction_id, effect FROM inserted NATURAL JOIN new_dividends;

table "transaction";

transaction_id	transaction_date	mutation
59b0f892-9c7b-4d00-b2c0-736169290221	2023-10-31 15:15:46.863376+00	mutation1

table new_dividends;

transaction_date	mutation	effect
2023-10-31 15:15:46.863376+00	mutation1	effect1

table dividend;

transaction_id	effect
59b0f892-9c7b-4d00-b2c0-736169290221	effect1