CODEWITHSUNDEEP
postgresql
Adrian
Adrian

Reputation: 1549

PostgreSQL - next serial value in a table

I have a simple question, suppose we have a table:

 id   A   B
 1   Jon  Doe
 2   Foo  Bar

Is there a way to know, which is the next id's increment, in this case 3 ? Database is PostgreSQL!

Tnx alot!

Upvotes: 55

Views: 70367

Answers (4)

Code on the Rocks
Code on the Rocks

Reputation: 17824

I'm new so here's the process I use having little to no prior knowledge of how Postgres/SQL work:

  1. Find the sequence for your table using pg_get_serial_sequence()

    SELECT pg_get_serial_sequence('person','id');

    This should output something like public.person_id_seq. person_id_seq is the sequence for your table.

  1. Plug the sequence from (1) into nextval()

    SELECT nextval('person_id_seq');

    This will output an integer value which will be the next id added to the table.

  1. You can turn this into a single command as mentioned in the accepted answer above

    SELECT nextval(pg_get_serial_sequence('person','id'));

  1. If you notice that the sequence is returning unexpected values, you can set the current value of the sequence using setval()

    SELECT setval(pg_get_serial_sequence('person','id'),1000);

    In this example, the next call to nextval() will return 1001.

Upvotes: 10

IMSoP
IMSoP

Reputation: 97996

If you want to claim an ID and return it, you can use nextval(), which advances the sequence without inserting any data.

Note that if this is a SERIAL column, you need to find the sequence's name based on the table and column name, as follows:

Select nextval(pg_get_serial_sequence('my_table', 'id')) as new_id;

There is no cast-iron guarantee that you'll see these IDs come back in order (the sequence generates them in order, but multiple sessions can claim an ID and not use it yet, or roll back an INSERT and the ID will not be reused) but there is a guarantee that they will be unique, which is normally the important thing.

If you do this often without actually using the ID, you will eventually use up all the possible values of a 32-bit integer column (i.e. reach the maximum representable integer), but if you use it only when there's a high chance you will actually be inserting a row with that ID it should be OK.

Upvotes: 100

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

To get the current value of a sequence without affecting it or needing a previous insert in the same session, you can use;

SELECT last_value FROM tablename_fieldname_seq;

An SQLfiddle to test with.

Of course, getting the current value will not guarantee that the next value you'll get is actually last_value + 1 if there are other simultaneous sessions doing inserts, since another session may have taken the serial value before you.

Upvotes: 23

Ivan Krechetov
Ivan Krechetov

Reputation: 19220

SELECT currval('names_id_seq') + 1;

See the docs

However, of course, there's no guarantee that it's going to be your next value. What if another client grabs it before you? You can though reserve one of the next values for yourself, selecting a nextval from the sequence.

Upvotes: 3

Related Questions