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.
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 |