Скачать или смотреть How to Perform Multiple Inserts Using One CTE in PostgreSQL

Learn how to insert data into multiple tables in PostgreSQL using a Common Table Expression (CTE) for seamless data manipulation.
---
This video is based on the question https://stackoverflow.com/q/63269546/ asked by the user 'Anand Sharma' ( https://stackoverflow.com/u/7691864/ ) and on the answer https://stackoverflow.com/a/63269845/ provided by the user 'Игорь Тыра' ( https://stackoverflow.com/u/12081543/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Multiple inserts using one cte postgres

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering PostgreSQL: Multiple Inserts Using One CTE

As a beginner in PostgreSQL, you may face various challenges when working with relational databases. One common challenge is how to efficiently insert data into multiple tables that are linked by relationships. In this guide, we'll tackle the problem of inserting data into two tables, where one table has a one-to-many relationship with the other, using a Common Table Expression (CTE).

Understanding the Problem

Imagine you have two tables: t1 and t2. The structure of these tables is as follows:

t1:

t1_id (primary key, serial)

t1_col (integer)

t2:

t2_id (primary key, serial)

t1_id (integer, foreign key referencing t1)

t2_col (integer)

In this scenario, t1 has a one-to-many relationship with t2, meaning that one entry in t1 can correspond to multiple entries in t2. The challenge lies in writing an SQL statement that first inserts data into t1, returns the inserted t1_id, and then utilizes that ID to insert multiple rows into t2.

Crafting the Solution

To accomplish this, we will use a Common Table Expression (CTE) along with the INSERT statement. Here is a step-by-step breakdown of the SQL statement you'll want to use:

Step 1: Insert Data into Table t1

First, you will need to insert data into t1 and retrieve the generated primary key (t1_id). This is done using the RETURNING clause within a CTE.

[[See Video to Reveal this Text or Code Snippet]]

The CTE, ins, will hold the result of your insert operation, specifically the newly created t1_id.

Step 2: Insert Multiple Rows into Table t2

Next, we need to insert multiple rows into t2 using the ID obtained from t1. Instead of directly using the VALUES clause, we can leverage the UNNEST function to expand an array of values. This allows you to insert multiple rows effectively.

[[See Video to Reveal this Text or Code Snippet]]

Here, the UNNEST function takes an array of values, such as [3, 4, 5], and pairs each of these with the t1_id from the ins CTE, inserting them into the t2 table.

Full Solution Example

Putting it all together, your complete SQL statement would look something like this:

[[See Video to Reveal this Text or Code Snippet]]

This method ensures that you effectively insert a record into t1 and simultaneously add related records to t2 without needing to run multiple statements.

Conclusion

Using a CTE for multiple inserts in PostgreSQL is a powerful technique that can streamline your database operations and maintain the integrity of your relational data. By following the structured approach outlined above, you can not only overcome the challenges of relational data insertion but also write clean, efficient SQL code.

Remember, SQL can be daunting at first, but with practice, you'll quickly become proficient. Happy querying!