Postgres: Get nextval in sequence without actually incrementing sequence?

Question

It looks like select nextval('table_name') actually does the value increment.

My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation.

I cannot run the select nextval on a read-only connection since it's actually trying to make a transaction. I would like to be able to query this and monitor the sequences on a read-only replica database.

How would you tackle this and meet the goal?

Answer 1

I would assume that SELECT currval('seq_name') + 1 or SELECT last_value + 1 FROM seq_name would be the answer, but that's only if you're incrementing by 1, or you know the increment off-hand.

If you're not incrementing by 1 and you don't know the increment, then try this:

SELECT currval('seq_name') + i.inc
  FROM (SELECT seqincrement AS inc
          FROM pg_sequence
         WHERE seqrelid = 'seq_name'::regclass::oid) AS i;

or

SELECT last_value + i.inc
  FROM seq_name,
      (SELECT seqincrement AS inc
         FROM pg_sequence
        WHERE seqrelid = 'seq_name'::regclass::oid) AS i;

Answer 2

Based on previous answers I realised that it fails for the first value added: The query returns 2 instead on 1 when there are no values added. Here is my solution:

SELECT (CASE WHEN is_called THEN last_value + i.inc 
        ELSE last_value END ) AS nextvalue
  FROM myserial,
      (SELECT seqincrement AS inc 
       FROM pg_sequence 
       WHERE seqrelid = 'myserial'::regclass::oid) AS i;

Answer 3

A little late to the game, but wanted to share this in case it is helpful.

SELECT sequencename 
    , data_type 
    , "last_value" 
    , start_value 
    , increment_by 
    , max_value 
FROM pg_catalog.pg_sequences
JOIN (
        SELECT regexp_split_to_array(pg_get_serial_sequence, E'\\.') AS seq_parts 
        FROM pg_get_serial_sequence('my_table', 'my_column')
    ) AS N
        ON N.seq_parts[2] = sequencename 

This should provide the data you're looking for to perform the calculations you need.