PostgreSQL: Last Inserted ID with INSERT RETURNING and CURRVAL
- Using
INSERT RETURNING
:
This is the preferred approach. When you perform an INSERT
statement, you can add the RETURNING
clause to specify columns you want to retrieve after the insert. This allows you to get the newly inserted ID in the same query.
For example:
INSERT INTO your_table (name, email) VALUES ('John Doe', '[email protected]')
RETURNING id;
This will insert the data and return the value in the id
column (assuming it's the primary key).
- Using Sequences and
CURRVAL
:
PostgreSQL often uses sequences to generate IDs for tables. These sequences automatically increment a value for each new row. You can access the current value of a sequence using the CURRVAL
function.
Here's how it works:
- Let's say your table's primary key is
id
and it uses the sequenceyour_table_id_seq
. - After an
INSERT
statement, you can use:
SELECT CURRVAL('your_table_id_seq');
This will return the ID value that was just generated for the inserted row.
Choosing the Right Method:
INSERT RETURNING
is generally recommended because it's more concise and efficient, especially if you need the ID right after insertion.- Use
CURRVAL
if you need the ID in a separate query after the insert or if your table doesn't use a sequence for the ID.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')
RETURNING id;
This code:
- Creates a table
users
with an auto-incrementing ID (SERIAL
) as the primary key. - Inserts a new user with username "john_doe" and email "[email protected]".
- Uses
RETURNING id
to retrieve the newly inserted ID value.
(Assuming the same users
table with id
as the primary key):
CREATE SEQUENCE users_id_seq; -- Create a sequence if it doesn't exist
INSERT INTO users (username, email) VALUES ('jane_doe', '[email protected]');
SELECT CURRVAL('users_id_seq');
- Creates a sequence named
users_id_seq
(if it doesn't already exist). This sequence generates the IDs for theusers
table (assumed). - Uses
CURRVAL('users_id_seq')
to retrieve the current value of the sequence, which represents the last inserted ID.
Note:
- In the second example, make sure the sequence name (
users_id_seq
) matches the actual sequence used by theid
column in your table. You can check this usingpg_get_serial_sequence('users','id');
.
- Using
RETURNING
with Information Functions:
While RETURNING
typically retrieves user-defined columns, you can combine it with information functions to get details about the inserted row. Here's an example:
INSERT INTO your_table (name, email) VALUES ('New User', '[email protected]')
RETURNING oid(ctid);
This code uses oid(ctid)
which returns the Object Identifier (OID) of the newly inserted row. It's not as convenient as the ID itself, but it can be useful in some scenarios.
Important Note: This approach is generally discouraged as it's less readable and might not be portable across different database systems.
- Triggers:
You can create a trigger that fires after an insert and retrieves the newly inserted ID using one of the methods mentioned earlier (e.g., CURRVAL
). This ID can then be stored in a separate table or variable within the trigger.
However, triggers add complexity and can impact performance. They are typically used for more advanced functionalities like data auditing or enforcing specific actions upon insert, not solely for retrieving the last inserted ID.
postgresql insert lastinsertid