PostgreSQL function for last inserted ID

by

( tl;dr : goto option 3: INSERT with RETURNING )

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

If you are interested in getting the id of a newly inserted row, there are several ways:

Option 1: CURRVAL(<sequence name>);.

For example:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

The name of the sequence must be known, it’s really arbitrary; in this example we assume that the table persons has an id column created with the SERIAL pseudo-type. To avoid relying on this and to feel more clean, you can use instead pg_get_serial_sequence:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

Caveat: currval() only works after an INSERT (which has executed nextval() ), in the same session.

Option 2: LASTVAL();

This is similar to the previous, only that you don’t need to specify the sequence name: it looks for the most recent modified sequence (always inside your session, same caveat as above).

Both CURRVAL and LASTVAL are totally concurrent safe. The behaviour of sequence in PG is designed so that different session will not interfere, so there is no risk of race conditions (if another session inserts another row between my INSERT and my SELECT, I still get my correct value).

However they do have a subtle potential problem. If the database has some TRIGGER (or RULE) that, on insertion into persons table, makes some extra insertions in other tables… then LASTVAL will probably give us the wrong value. The problem can even happen with CURRVAL, if the extra insertions are done intto the same persons table (this is much less usual, but the risk still exists).

Option 3: INSERT with RETURNING 

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

This is the most clean, efficient and safe way to get the id. It doesn’t have any of the risks of the previous.

Drawbacks? Almost none: you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it’s not standard SQL (who cares); it’s available since Postgresql 8.2 (Dec 2006…)

Conclusion: If you can, go for option 3. Elsewhere, prefer 1.

Note: all these methods are useless if you intend to get the last inserted id globally (not necessarily by your session). For this, you must resort to SELECT max(id) FROM table (of course, this will not read uncommitted inserts from other transactions).

Conversely, you should never use SELECT max(id) FROM table instead one of the 3 options above, to get the id just generated by your INSERT statement, because (apart from performance) this is not concurrent safe: between your INSERT and your SELECT another session might have inserted another record.

More Related Contents:

  1. Insert, on duplicate update in PostgreSQL?
  2. How does the search_path influence identifier resolution and the “current schema”
  3. How do you find the row count for all your tables in Postgres
  4. PostgreSQL error: Fatal: role “username” does not exist
  5. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  6. See and clear Postgres caches/buffers?
  7. “psql: could not connect to server: Connection refused” Error when connecting to remote database
  8. postgresql duplicate key violates unique constraint
  9. ClassNotFoundException with PostgreSQL and JDBC
  10. INSERT rows into multiple tables in a single query, selecting from an involved table
  11. How to get function parameter lists (so I can drop a function)
  12. postgresql: INSERT INTO … (SELECT * …)
  13. How to convert a postgres database to sqlite
  14. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  15. Error when setting n_distinct using a plpgsql variable
  16. How can I change a PostgreSQL user password?
  17. Docker wait for postgresql to be running
  18. Function to loop through and select data from multiple tables
  19. SQL query column does not exist error
  20. Switch role after connecting to database
  21. SQL Sub queries in check constraint
  22. When do Postgres column or table names need quotes and when don’t they?
  23. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  24. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  25. PostgreSQL how to concat interval value ‘2 days’
  26. Displaying image in Ireports using PostgreSql
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment