100

How is it possible to get the current sequence value in postgresql 8.4?

Note: I need the value for the some sort of statistics, just retrieve and store. Nothing related to the concurrency and race conditions in case of manually incrementing it isn't relevant to the question.

Note 2: The sequence is shared across several tables

Note 3: currval won't work because of:

  • Return the value most recently obtained by nextval for this sequence in the current session
  • ERROR: currval of sequence "<sequence name>" is not yet defined in this session

My current idea: is to parse DDL, which is weird

zerkms
  • 249,484
  • 69
  • 436
  • 539

3 Answers3

200

You may use:

SELECT last_value FROM sequence_name;

Update: this is documented in the CREATE SEQUENCE statement:

Although you cannot update a sequence directly, you can use a query like:

SELECT * FROM name;

to examine the parameters and current state of a sequence. In particular, the last_value field of the sequence shows the last value allocated by any session. (Of course, this value might be obsolete by the time it's printed, if other sessions are actively doing nextval calls.)

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • That's it! Could you please also add a reference to a documentation that explains that we can use sequence in that way and I'll check you. – zerkms Feb 15 '13 at 00:13
17

If the sequence is being used for unique ids in a table, you can simply do this:

select max(id) from mytable;

The most efficient way, although postgres specific, is:

select currval('mysequence');

although technically this returns the last value generated by the call to nextval('mysequence'), which may not necessarily be used by the caller (and if unused would leave gaps in an auto increments id column).

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 5
    `max(id)` doesn't guarantee to be even close to the sequence value. PS: also forgot to mention that the sequence is shared across several tables, sorry for that – zerkms Feb 14 '13 at 23:44
  • 23
    `currval` won't work - because it returns the latest value within the current session. I don't call `nextval` in the current session at all, so `ERROR: currval of sequence "" is not yet defined in this session` – zerkms Feb 15 '13 at 00:06
11

As of some point in time, a secret function (which is undocumented ... still), does exactly this:

SELECT pg_sequence_last_value('schema.your_sequence_name');

Update: Turns out this function isn't 100% production-safe. The function name is misleading - its not the last issued value, but rather the last saved/cached value to disk. That means it can show higher numbers than actual.

Alexi Theodore
  • 1,177
  • 10
  • 16
  • Why undocumented function if there is documented `SELECT last_value FROM sequence_name;`? – zerkms Sep 08 '22 at 08:20
  • 1
    I cannot say why the original authors made the function, but I can say its a small convenience that is appreciated for some cases that I use such as in functions or when comparing its change over time within a single query. Certainly all that could be done with the "FROM" version too. – Alexi Theodore Sep 09 '22 at 16:08