How to use results from first query in later queries within a DB Transaction

Question

A common case for DB transactions is performing operations on multiple tables, as you can then easily rollback all operations if one fails. However, a common scenario I run into is wanting to insert records to multiple tables where the later inserts need the serial ID from the previous inserts.

Since the ID is not generated/available until the transaction is actually committed, how can one accomplish this? If you have to commit after the first insert in order to get the ID and then execute the second insert, it seems to defeat the purpose of the transaction in the first place because after committing (or if I don't use a transaction at all) I cannot rollback the first insert if the second insert fails.

This seems like such a common use case for DB transactions that I can't imagine it would not be supported in some way. How can this be accomplished?

Answer 1

cte (common table expression) with data modifying statements should cover your need, see the manual.

Typical example :

WITH cte AS (INSERT INTO table_A (id) VALUES ... RETURNING id)
INSERT INTO table_B (id) SELECT id FROM cte

see the demo in dbfiddle