Select Max Serial Key Sql Postgres

Active11 months ago

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

Select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one. PostgreSQL MAX function is an aggregate function that returns the maximum value in a set of values. The MAX function is useful in many cases. For example, to find the employees with the highest salary, or to find the most expensive products, etc.

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

Tnx alot!

Postgres Max Function

AdrianAdrian
5993 gold badges8 silver badges24 bronze badges

Postgres Select From Table

Select max serial key sql postgresql

3 Answers

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:

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.

IMSoPIMSoP
50.4k6 gold badges63 silver badges101 bronze badges

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

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.

Joachim IsakssonJoachim Isaksson
148k18 gold badges219 silver badges251 bronze badges

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.

Ivan KrechetovIvan Krechetov

Postgres Sql Statement

13.9k8 gold badges42 silver badges57 bronze badges

Edraw Max Serial Key

Not the answer you're looking for? Browse other questions tagged postgresql or ask your own question.