How to Insert Data into Multiple Related Postgres Tables in a Single Query?

Single-Query Data Insertion Across Multiple Related Postgres Tables

This guide addresses the challenge of inserting data into multiple interconnected Postgres tables using a single SQL query, specifically when needing to retrieve a primary key from an initial insertion and utilize it as a foreign key in subsequent insertions.

The Problem: Efficiently inserting data into three or more related tables within a single query, ensuring proper foreign key relationships are maintained.

Solution: Leveraging Data-Modifying CTEs

Common Table Expressions (CTEs) offer an elegant solution. Data-modifying CTEs allow sequential INSERT operations, where each subsequent insertion relies on the results of the preceding one.

Implementation Example:

The following demonstrates inserting data into three tables (sample, sample1, sample2) using data-modifying CTEs:

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;

Explanation:

1. ins1: Inserts a row into the sample table and returns the newly generated id (primary key) as sample_id.
2. ins2: Utilizes the sample_id from ins1 to insert a row into sample1, returning the generated user_id.
3. Final INSERT: Employs the user_id from ins2 to insert data into sample2.

Alternative: Batch Insertion with CTEs

This approach handles multiple data rows simultaneously:

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);

This efficiently inserts multiple rows by defining them within the data CTE.

Important Considerations:

• Foreign Key Constraints: Ensure appropriate foreign key constraints are defined to maintain referential integrity.
• ON CONFLICT Clause: Utilize ON CONFLICT to manage potential duplicate key errors.
• Concurrency: Be mindful of potential race conditions during concurrent data writes.

This comprehensive approach provides a robust and efficient method for managing data insertion across related Postgres tables within a single query.

The above is the detailed content of How to Insert Data into Multiple Related Postgres Tables in a Single Query?. For more information, please follow other related articles on the PHP Chinese website!