How Can PostgreSQL CTEs and INSERT...RETURNING Efficiently Insert Data into Parent and Child Tables?

Inserting Multiple Values into Child Tables Using PostgreSQL's INSERT...RETURNING and CTEs

To insert a newly extracted user_id from the parent table table1 into its child tables table2 and table3, PostgreSQL provides an elegant solution using data-modifying Common Table Expressions (CTEs).

Using CTEs, we can chain several INSERT statements to execute multiple operations in a single transaction:

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;

This code follows a step-by-step process:

1. ins1: Inserts a new row into table1 and retrieves the newly generated user_id using RETURNING. This step establishes the connection between the child tables.
2. ins2: Inserts the user_id from ins1 into table2 along with the password.
3. Inserts the user_id from ins1 into table3 with additional data such as address, city, and phone.

Key Points:

• It's advisable to provide a column definition list for INSERTs to ensure data integrity.
• Default values are automatically inserted, so columns with default values can be omitted in the INSERT statement.
• The final RETURNING clause in the third INSERT statement returns the user_id from table3, although it's typically the same as the user_id from table1.
• Data-modifying CTEs allow chaining multiple INSERTs and other data manipulation operations into a single transaction.

The above is the detailed content of How Can PostgreSQL CTEs and INSERT...RETURNING Efficiently Insert Data into Parent and Child Tables?. For more information, please follow other related articles on the PHP Chinese website!