Postgresql function for last inserted id

Question

I am looking for function in postgres that returns last inserted id into table. In MS SQL there is SCOPE_IDENTITY(). Is something same in PostgreSQL?

Please, do not advise use something like this:

select max(id) from table

Answer 1

SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))

You need to supply the table name and column name of course.

This will be for the current session / connection http://www.postgresql.org/docs/8.3/static/functions-sequence.html

Answer 2

See the below example

CREATE TABLE users ( -- make the "id" column a primary key; this also creates -- a UNIQUE constraint and a b+-tree index on the column id SERIAL PRIMARY KEY, name TEXT, age INT4 );

INSERT INTO users (name, age) VALUES ('Mozart', 20);

Then for getting last inserted id use this for table "user" seq column name "id"

SELECT currval(pg_get_serial_sequence('users', 'id'));

Thanks,

Rinson KE, DBA 91+ 9995044142 www.qburst.com

Answer 3

See the RETURNING clause of the INSERT statement. Basically, the INSERT doubles as a query and gives you back the value that was inserted.

Answer 4

Recall that in postgresql there is no "id" concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type). Sequences have some pecularities to deal with transaction isolation.

If you are interested in getting the id of a just inserted row (inside your "session"), you have two or three approaches, all efficient and safe:

1. SELECT CURRVAL(<sequence name>); . Typically used in conjunction with pg_get_serial_sequence

2. SELECT LASTVAL(); Similar to previous, sequence name not needed. Typically also right, if called just after your insert. (And no, there are no concurrency problems, sessions are isolated).

3. Return the id in the RETURNING expression of INSERT Alternative approach, does not require an extra query, but you must modify your insert query.

Notice that none of this methods work if you intend to get the last inserted id globally (i.e. not necessarily in your session). For this, you must resort to select max(id) from table (of course, this will not read uncommitted inserts from other transactions).