How Can PostgreSQL's CTEs Efficiently Handle Multiple INSERTs with RETURNING for Multi-Table Updates?

Multiple INSERT...RETURNING for Multi-Table Updates in PostgreSQL

Inserting data into multiple child tables while referencing newly inserted data from a parent table can be a challenging task. In this context, PostgreSQL provides a powerful solution using data-modifying common table expressions (CTEs).

Initially, we can retrieve the user_id of a newly inserted row in a parent table using the RETURNING clause:

INSERT INTO table1 (default,'johnee','john','smith',default) RETURNING userid;

To perform the multiple INSERTs, we can utilize data-modifying CTEs as follows:

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;

In this code, the ins1 CTE performs the INSERT into table1 and returns the user_id. The ins2 CTE then uses the user_id from ins1 to insert data into table2. Finally, the third INSERT uses the user_id from ins1 to insert data into table3, optionally returning the resulting user_id.

By using data-modifying CTEs, we can efficiently chain multiple INSERTs and ensure that the newly inserted data in child tables maintains the necessary relationships with the parent table.

The above is the detailed content of How Can PostgreSQL's CTEs Efficiently Handle Multiple INSERTs with RETURNING for Multi-Table Updates?. For more information, please follow other related articles on the PHP Chinese website!