Скачать или смотреть How to Insert into Multiple Tables in PostgreSQL and Return Results

Learn how to efficiently insert records into multiple tables in PostgreSQL and retrieve them using stored procedures.
---
This video is based on the question https://stackoverflow.com/q/69674218/ asked by the user 'Szilvási Dávid' ( https://stackoverflow.com/u/10897238/ ) and on the answer https://stackoverflow.com/a/69675143/ provided by the user 'Edouard' ( https://stackoverflow.com/u/8060017/ ) 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: postgres insert into multiple tables after each other and return everything

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.
---
Managing Multi-Table Inserts in PostgreSQL

When working with relational databases, it's common to have to insert data into multiple tables in a single transaction. This is especially true in systems where data pertaining to users has to link with other data such as URLs. If you've been wrestling with this in PostgreSQL, you're not alone. Today, we’re going to walk through how to insert into multiple tables and return the results effectively.

The Problem

Imagine you have a PostgreSQL database with the following structure:

users: This table stores user information and is linked by user_id.

urls: This table holds URL slugs along with their long URL counterparts.

userlinks: This acts as a junction table to map users to URLs, allowing multiple users to associate with multiple URLs.

When a new URL slug is created by a user, you may want to do the following:

Insert the new slug into the urls table.

Get the slug_id of the newly created slug.

Insert a record into the userlinks table using the current user's ID (accessible via auth.uid()) and the slug_id obtained from the first insertion.

Finally, return the combined results from both insertions.

It sounds straightforward but could become complex, especially when trying to return results from multiple tables. Let’s break down a workable solution.

The Solution

Here’s how you can structure your SQL query to achieve this task seamlessly. Below, we’ll walk through each part of the SQL query provided:

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

Explanation of the Code

The WITH Clause:

This clause starts with urls_row, where we insert a new URL slug and long URL into the urls table. The RETURNING slug_id clause enables us to fetch the slug_id of the inserted row immediately.

Inserting into Userlinks:

The second part of the WITH clause is userlink_row. Here, we insert into the userlinks table using a SELECT statement to get the user_id from auth.uid() and the slug_id we just obtained from urls_row.

Returning Combined Results:

Finally, we perform a SELECT statement joining urls_row and userlink_row on slug_id. This way, we can return all relevant data from both tables in one single output.

Benefits of This Approach

Atomic Transactions: The use of a common table expression (WITH) ensures that all parts of the operation are managed in a single transaction, reducing the likelihood of data inconsistency.

Efficiency: By using RETURNING, we minimize extra queries that would otherwise require additional database calls, effectively reducing load times.

Conclusion

Handling multi-table inserts in PostgreSQL doesn’t have to be cumbersome. With the right structure—leveraging temporary results via common table expressions—you can perform operations efficiently while ensuring that you retrieve all necessary information. By following the provided query example, you'll not only streamline your data insertion but also enhance performance in your applications.

Feel free to reach out in the comments with questions or share your experiences with multi-table inserts in PostgreSQL!