Options to retrieve the current (on a moment of running query) sequence value
PostgresqlPostgresql 8.4Postgresql Problem Overview
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
Postgresql Solutions
Solution 1 - Postgresql
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.)
Solution 2 - Postgresql
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).