How Can PostgreSQL CTEs Efficiently Insert Multiple Rows Across Multiple Tables?

Inserting Multiple Rows with PostgreSQL's CTEs

Suppose you have a relational database with three tables: table1, table2, and table3. Table table1 holds general user information, while table table2 and table3 contain additional data such as passwords and addresses. As you add a new user in table1, you want to simultaneously insert the user ID and other unique data into the child tables table2 and table3.

Postgres 9.3's data-modifying CTEs provide a solution to this scenario. CTEs allow you to chain multiple INSERT operations efficiently.

Here's how to use data-modifying CTEs to accomplish your task:

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
    -- Nothing to return here
)
INSERT INTO table3 (user_id, address, city, phone)
SELECT ins1.user_id, ..., ...
FROM ins1
RETURNING user_id;

Breakdown of the CTE:

• ins1 CTE: Inserts a new row into table1 and returns the newly created user_id.
• ins2 CTE: Uses the user_id from ins1 to insert data into table2. Since there's no need to return a value, it's omitted.
• Final INSERT: Inserts data into table3 using the user_id from ins1 and additional columns. You can specify the desired columns and values as needed. The optional RETURNING clause allows you to retrieve the newly created user_id from table3.

Best Practices:

• Always include a column definition list in your INSERT statements to ensure consistency with your table structure.
• If a column should receive a default value, simply omit it in the INSERT statement; Postgres will automatically insert the default.
• The last CTE can optionally return a value, which could be useful for further processing or for retrieving newly created IDs.

The above is the detailed content of How Can PostgreSQL CTEs Efficiently Insert Multiple Rows Across Multiple Tables?. For more information, please follow other related articles on the PHP Chinese website!