How Can Postgres CTEs Help Insert Data into Multiple Tables Simultaneously?

Using Postgres CTEs for Concurrent Data Insertion Across Multiple Tables

Efficiently inserting data into multiple interconnected database tables is crucial for managing complex data relationships. PostgreSQL's Common Table Expressions (CTEs), specifically data-modifying CTEs, offer a robust solution.

Consider a scenario involving three tables: sample, sample1, and sample2, defined as follows:

CREATE TABLE sample (
   id        bigserial PRIMARY KEY,
   lastname  varchar(20),
   firstname varchar(20)
);

CREATE TABLE sample1(
   user_id    bigserial PRIMARY KEY,
   sample_id  bigint REFERENCES sample,
   adddetails varchar(20)
);

CREATE TABLE sample2(
   id      bigserial PRIMARY KEY,
   user_id bigint REFERENCES sample1,
   value   varchar(10)
);

The challenge lies in reusing generated keys from one insertion for subsequent insertions into related tables. Data-modifying CTEs elegantly solve this:

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;

This chained CTE approach ensures sequential insertion, with each INSERT contingent on the success of its predecessor. Using SELECT instead of VALUES guarantees that downstream table insertions only proceed if the preceding INSERT completes successfully.

Alternatively, a more centralized approach uses a single CTE to define data rows:

WITH data(firstname, lastname, adddetails, value) AS (
   VALUES ('fai55', 'shaggk', 'ss', 'ss2')
   ),
ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT DISTINCT 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);

This method requires careful handling of duplicate (firstname, lastname) combinations. Concurrent write considerations are also important and require further investigation (see additional resources for details).

The above is the detailed content of How Can Postgres CTEs Help Insert Data into Multiple Tables Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!