How to Retrieve the last inserted id After a Multiple Insert in PostgreSQL

Описание: This guide explains how to retrieve the last inserted ID after performing multiple inserts in PostgreSQL using a Common Table Expression (CTE). Learn how to effectively use the RETURNING clause.
---
This video is based on the question https://stackoverflow.com/q/76262299/ asked by the user 'e1s' ( https://stackoverflow.com/u/4675431/ ) and on the answer https://stackoverflow.com/a/76262432/ provided by the user 'Frank Heikens' ( https://stackoverflow.com/u/271959/ ) 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: Return last id of multiple insert

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 Multiple Inserts in PostgreSQL: How to Retrieve the Last Inserted ID

When using PostgreSQL, one common challenge developers face is retrieving the last inserted id after a multiple insert operation. In various applications, it’s crucial to know what the last ID was, especially if you need to reference it later. While many may think that using SELECT MAX(id) from the table is the solution, it’s not the correct approach for multiple inserts since it may not consistently return the ID of the last record added. This post will guide you through the steps for achieving this using a Common Table Expression (CTE) and the RETURNING clause.

Understanding the Problem

Let’s consider a common scenario: You want to insert multiple rows into your PostgreSQL table and immediately retrieve the ID of the last inserted row. The confusion often arises because the basic use of the RETURNING clause works for single inserts but leads to errors or unexpected results when used for multiple inserts.

Example of the Confusion

Imagine you are executing the following code:

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

This attempt will result in an error as PostgreSQL cannot directly assign multiple return values into a single variable.

The Solution: Using CTE with RETURNING

To properly retrieve the last ID inserted during a multiple insert operation, you can harness the power of a Common Table Expression (CTE). Below are detailed steps that you can follow to achieve this.

Step-by-Step Explanation

Setting Up the Temporary Table
First, we create a temporary table for the demonstration:

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

Performing a Single Insert and Returning the ID
You can still use the RETURNING clause for single inserts. For instance:

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

Using a CTE for Multiple Inserts
To get the last inserted ID from multiple inserts, use a CTE combined with the RETURNING clause:

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

Full Code Example

Here is a complete code snippet demonstrating all of the steps mentioned:

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

Conclusion

By implementing the above approach, you can efficiently retrieve the last inserted ID after multiple insert operations in PostgreSQL. This technique not only resolves the immediate problem but also enhances your understanding of how to leverage CTEs and the RETURNING clause in PostgreSQL.

In summary, remember:

Use RETURNING with single inserts for immediate ID retrieval.

Employ a CTE to capture multiple insert IDs and then use aggregation to select the maximum ID.

Feel free to apply these techniques in your PostgreSQL development tasks, and take control over your data insertion processes!