56

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!

Improve this question
4
  • take a look at the nextval-method: postgresql.org/docs/8.1/static/functions-sequence.html Commented Aug 14, 2013 at 13:15
  • 2
    In fact, it can be anything above select MAX(id) FROM the_table) Why do you want to know? Commented Aug 14, 2013 at 13:15
  • well, Im doing ON DUPLICATE KEY emulation from MySQL.. it works like this : php checks if we have an id in object we want to save, if yes, then okay, if no, get from db next id, after that, 'ON DUPLICATE KEY' query will execute Commented Aug 14, 2013 at 13:19
  • 1
    Hm. Not quite clear. Why not just insert the object without any ID, and then query for the just used ID (automatically popped from the sequence) – php.net/manual/en/pdo.lastinsertid.php Commented Aug 14, 2013 at 13:28

4 Answers 4

Reset to default
100

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Can you provide a source for There is no cast-iron guarantee of what order these IDs will come back in? It looks like the nextval() link you provided says the opposite - that nextval cannot rollback, so although there may be gaps, the next val will always be incremental.
@TheJKFever Yes, strictly speaking, they are guaranteed to be given out in order; but since you can't see what other transactions are doing, it may not look that way from inside a particular piece of code. For instance, if you call nextval twice in a row you might get IDs 1 and 5 rather than 1 and 2; or if you manually insert the result into a table, you might find row 3 is inserted before row 2. I'll try to think of a better wording for that sentence.
"You will eventually use up all possible values" - but that will take a long time. If you burn 10000 values per second 24 hours a day 7 days a week it will take 29247120 years to "use up" all values.
@a_horse_with_no_name Only if you're using a 64-bit (bigint) sequence; for a 32-bit sequence starting at 1, e.g. a standard serial column, that rate of use would use up all values in under 3 days. I've had real-world processes which used up the range in a matter of weeks (but was able to safely have the sequence cycle, rather than widening the column).
@a_horse_with_no_name What use is that when you try to insert the 2147483649th value into the column? If I'm binding a sequence to a 32-bit value, I would expect its upper limit to be 2^31, because that's the highest usable value. The internal implementation could be arbitrary-precision arithmetic for all I care, that's still the highest integer that's representable in my application, and if I call nextval enough times, I will reach that limit, and I will encounter errors.
|
23

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.

Improve this answer

1 Comment

Note that it is not recommended to rely on the naming of sequences generated for SERIAL columns, as this is considered an implementation detail and subject to change. The pg_get_serial_sequence() function will give you the name of the sequence "owned" by a particular column.
10

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.

Improve this answer

Comments

3

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.

Improve this answer

1 Comment

Do you mean CURRVAL? Afaik, it's not set until the current session has done an insert to the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.