• 1
Votes
name
name Punditsdkoslkdosdkoskdo

PostgreSQL next value of the sequences?

I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. I am able to do this using grocery crud's callback_after_upload function.

I want a next id of the content while adding a new content. I tried to use nextval() function, but sequence gets incremented with it. How can get the last value of the sequence without using nextval() function?

Or is there a simple way I can do this?

RETURNING

In modern-day PostgreSQL (8.2 or later) you do this with a single round-trip to the database:

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

tbl_id being a  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' || lastval())  -- or currval('tbl_tbl_id_seq')
RETURNING tbl_id;

The manual about lastval():

This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session.

If you have (or can have) multiple sequences linked to the table (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 

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 anysequence if so defined. And if the default name is taken when creating the table, Postgres picks the next free name according to a hard-coded algorithm.

If you don't know the name of the sequence for a serial column, look it up with the dedicated function pg_get_serial_sequence(). Can even be done on the fly:

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

SQL Fiddle.

  • 0
Reply Report