PostgreSQL: Last Inserted ID with INSERT RETURNING and CURRVAL

1. 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).

1. 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 sequence your_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 the users 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 the id column in your table. You can check this using pg_get_serial_sequence('users','id');.

1. 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.

1. 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.