PostgreSQL - next sequential value in a table

Question

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

id AB 1 Jon Doe 2 Foo Bar 

Is there any way to find out what is the next step id, in this case 3? Database - PostgreSQL!

Tnx alot!

Answer 1

If you want to get the identifier and return it, you can use nextval() , which promotes the sequence without inserting any data.

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

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

There is no cast-iron guarantee of the order in which these identifiers will be returned (since several sessions may require the identifier and not use it, or even roll back INSERT ), but there is a guarantee that they will be unique, which is usually important.

If you often do this without actually using an identifier, you will end up using all the possible values ​​of the column (i.e. reach the maximum number represented), but if you use it only when there is a high probability that you will really be inserting a line with this identifier, it should be in order.

Answer 2

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

 SELECT last_value FROM tablename_fieldname_seq; 

SQLfiddle for testing with .

Of course, getting the current value does not guarantee that the next value you get is actually last_value + 1 , if there are other simultaneous sessions doing inserts, since the other session may have taken the serial value in front of you.

Answer 3

SELECT currval('names_id_seq') + 1;

See docs

However, of course, there is no guarantee that this will be your next value. What if another client takes it in front of him? You can reserve one of the following values ​​for yourself by choosing nextval from the sequence.