Postgres: Get nextval in sequence without actually incrementing sequence?

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?

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;

StackExchange Database Administrators Q#253090, answer score: 16