PostgreSQL next value of the sequences?

by

RETURNING

That’s possible with a single round-trip to the database:

INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;

tbl_id would typically be a serial or IDENTITY (Postgres 10 or later) column. More in the manual.

Explicitly fetch value

If filename needs to include tbl_id (redundantly), you can still use a single query.

Use lastval() or the more specific currval():

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval('tbl_tbl_id_seq')   -- or lastval()
RETURNING tbl_id;

See:

If multiple sequences may be advanced in the process (even by way of triggers or other side effects) the sure way is to use currval('tbl_tbl_id_seq').

Name of sequence

The string literal 'tbl_tbl_id_seq' in my example is supposed to be the actual name of the sequence and is cast to regclass, which raises an exception if no sequence of that name can be found in the current search_path.

tbl_tbl_id_seq is the automatically generated default for a table tbl with a serial column tbl_id. But there are no guarantees. A column default can fetch values from any sequence if so defined. And if the default name is taken when creating the table, Postgres picks the next free name according to a simple algorithm.

If you don’t know the name of the sequence for a serial column, use the dedicated function pg_get_serial_sequence(). Can be done on the fly:

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;

db<>fiddle here
Old sqlfiddle

More Related Contents:

  1. PostgreSQL gapless sequences
  2. CodeIgniter Disallowed Key Characters
  3. subquery in codeigniter active record
  4. Why does PostgreSQL perform sequential scan on indexed column?
  5. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  6. I forgot the password I entered during postgres installation
  7. In-order sequence generation
  8. Codeigniter – no input file specified
  9. codeigniter CSRF error: “The action you have requested is not allowed.”
  10. Postgres analogue to CROSS APPLY in SQL Server
  11. Generate series of dates – using date type as input
  12. Codeigniter image resize?
  13. Removing specific items from array with MongoDB
  14. How to rewrite the index.php of Codeigniter on Windows Azure
  15. HTTP OPTIONS error in Phil Sturgeon’s Codeigniter Restserver and Backbone.js
  16. Using Mysql WHERE IN clause in codeigniter
  17. How to pass custom type array to Postgres function
  18. Test for null in function with varying parameters
  19. Postgres function returning table not returning data in columns
  20. Constructor session validation for different functions
  21. How to upload image in CodeIgniter?
  22. How to reset sequence in postgres and fill id column with new data?
  23. Correct naming structure for CodeIgniter
  24. How to integrate codeIgniter with netbeans fully
  25. Where do I put image files, css, js, etc. in Codeigniter?
  26. How to use SQL LIKE condition with multiple values in PostgreSQL?
  27. now() default values are all showing same timestamp
  28. Join a count query on generate_series() and retrieve Null values as ‘0’
  29. codeigniter : Commands out of sync; you can’t run this command now
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment