How Can I Efficiently Chain Multiple INSERT Statements with RETURNING in PostgreSQL?

Chaining Multiple INSERTs with RETURNING in PostgreSQL

When working with hierarchical database structures, it's often necessary to insert data into multiple tables with dependencies. In PostgreSQL, a combination of data-modifying CTEs and the RETURNING clause provides an efficient solution.

To insert a new row into the parent table table1 and populate the relevant rows in the child tables table2 and table3, follow these steps:

WITH ins1 AS (
   INSERT INTO table1 (username, name, surname)
   VALUES ('johnee','john','smith')
   RETURNING user_id
   )
, ins2 AS (
   INSERT INTO table2 (user_id, password)
   SELECT ins1.user_id, 'secret'
   FROM   ins1
   )
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM   ins1
RETURNING user_id;

Breaking Down the CTE Chain:

• The first CTE, ins1, inserts a new row into table1 and returns the generated user_id.
• The second CTE, ins2, inserts a corresponding row into table2 with the user_id returned from ins1.
• The final INSERT statement uses the user_id from ins1 to insert a row into table3. The optional RETURNING clause returns the resulting user_id, typically from a sequence or default value.

Benefits of Using Data-Modifying CTEs:

• Chaining Inserts: CTEs allow you to chain multiple INSERT statements, ensuring the correct order of operations.
• Simplicity: The use of WITH CTEs makes the query more readable and maintainable.
• Efficiency: CTEs can improve performance by optimizing the execution order of the INSERT statements.

The above is the detailed content of How Can I Efficiently Chain Multiple INSERT Statements with RETURNING in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!