49

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!

Adrian
  • 1,499
  • 3
  • 19
  • 26
  • take a look at the nextval-method: http://www.postgresql.org/docs/8.1/static/functions-sequence.html – Mirco Widmer Aug 14 '13 at 13:15
  • 2
    In fact, it can be anything above `select MAX(id) FROM the_table)` **Why** do you want to know? – wildplasser Aug 14 '13 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 – Adrian Aug 14 '13 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) – http://php.net/manual/en/pdo.lastinsertid.php – Ivan Krechetov Aug 14 '13 at 13:28

4 Answers4

94

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.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • 1
    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 Sep 07 '18 at 04:16
  • @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. – IMSoP Sep 07 '18 at 08:34
  • "*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. –  Sep 07 '18 at 09:24
  • 1
    @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](https://www.wolframalpha.com/input/?i=2%5E31+%2F+10000+seconds). 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). – IMSoP Sep 07 '18 at 09:44
  • @IMSoP: a `serial` column uses a bigint sequence by default. Only the _column_ is defined as `int` –  Sep 07 '18 at 09:45
  • 1
    @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. – IMSoP Sep 07 '18 at 09:53
  • @a_horse_with_no_name I went to edit a clarification into my answer, but discovered it *already* says "all the possible values **of the column**" so your pedantry about the sequence itself being 64-bit is irrelevant. However, I've mentioned "32-bit" explicitly, just to be clear that that was my assumption. – IMSoP Sep 07 '18 at 09:56
20

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.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • 3
    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()`](http://www.postgresql.org/docs/current/static/functions-info.html) function will give you the name of the sequence "owned" by a particular column. – IMSoP Aug 14 '13 at 13:30
4

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.

Code on the Rocks
  • 11,488
  • 3
  • 53
  • 61
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.

Ivan Krechetov
  • 18,802
  • 8
  • 49
  • 60