How can I insert data into multiple Postgres tables simultaneously while maintaining data integrity?

Insert data into multiple tables simultaneously in PostgreSQL

Using a single query to insert data into multiple tables ensures consistency and efficiency of database operations. This can be achieved by using a data modifying common table expression (CTE) as shown below:

Modify CTE using data:

The following query uses a data modification CTE to continuously insert data into three tables:

<code class="language-sql">WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
   RETURNING id AS sample_id
   ),
ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;</code>

Each INSERT depends on the previous INSERT, ensuring the correct keys are obtained and used in subsequent inserts.

Alternatively, by providing the complete row of data:

A more convenient way is to use CTE to provide the complete row of data in one place:

<code class="language-sql">WITH data(firstname, lastname, adddetails, value) AS (
   VALUES                               
      ('fai55', 'shaggk', 'ss', 'ss2'),  
      ('fai56', 'XXaggk', 'xx', 'xx2')       
   )
, ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT firstname, lastname FROM data
   RETURNING firstname, lastname, id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT ins1.sample_id, d.adddetails
   FROM   data d
   JOIN   ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT ins2.user_id, d.value
FROM   data d
JOIN   ins1 USING (firstname, lastname)
JOIN   ins2 USING (sample_id);</code>

This method allows multiple rows of data to be inserted, and it handles duplicate rows by selecting a different value for the first INSERT.

Solving concurrency and unique constraint issues:

To resolve potential concurrency issues and ensure data integrity, you can add sample constraints in the (firstname, lastname) columns of the UNIQUE table, and you can use the INSERT clause in the ON CONFLICT query, as below as described in the linked reference.

The above is the detailed content of How can I insert data into multiple Postgres tables simultaneously while maintaining data integrity?. For more information, please follow other related articles on the PHP Chinese website!