0

When I used this query to see the current primary key seed of my PostgreSQL 13 table:

select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id')) 

it told me:

SQL Error [55000]:
ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

The id column is defined like this:

ALTER TABLE rss_sub_source 
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY 
        (START WITH 1233);

Table definition:

CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    --  ... many more columns
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

How to see the current auto-increment seed for the primary key?

2

The functions currval(regclass) and lastval() only make sense after nextval() has been used in the same session (not transaction!). Concurrency control in PostgreSQL is reason behind this behavior.
If you are just curious about the current state of the sequence (not the last serial ID actually used in the session), you can always just call nextval():

SELECT nextval(pg_get_serial_sequence('foo', 'id'));

Returns the next serial ID, which had not been used, yet. This carries the flaw of actually consuming the returned ID (which should not matter in a proper relational design). But that side effect can be avoided. In Postgres, serial and IDENTITY columns are implemented using a SEQUENCE internally, which happens to be a table-like object that can be queried like a table. If you are curious about the current state of a SEQUENCE, just use SELECT:

SELECT * FROM public.rss_sub_source_id_seq;

(Requires the SELECT privilege on the sequence, while the above mentioned sequence functions require the USAGE privilege.)
You need to know the name of the sequence for this, and you already know how to get it: pg_get_serial_sequence('rss_sub_source', 'id'). It's easy enough to guess, too. See:

For ad-hoc use, just compile the query by hand. Or you can use \gexec in psql:

SELECT 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence('b2', 'big_id')\gexec

See:

For automated (or regular) use, you need dynamic SQL to use the text value returned by the function as SQL identifier. Here is a simple custom function to do that. Also factored in is_called:

CREATE OR REPLACE FUNCTION sequence_currval(_tbl text, _col text, OUT currval bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
  EXECUTE 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence(_tbl, _col)
  INTO currval;
END
$func$;

Call:

SELECT sequence_currval('foo', 'id');

This is safe against SQLi because pg_get_serial_sequence() returns the identifier safely double-quoted as needed.

The function returns the last value that has been consumed from this sequence. (Does not mean it made its way into the table.) The next one will be greater. (Assumes a default sequence with INCREMENT 1!)

It does not consume a serial ID and does not require that nextval() has been called in the same session.

Consider this demo:
db<>fiddle here

1

Normally you want the value sequence value you just generated, or cause to be generated, and then using lastval is the right way to get it, but you need to have provoked a nextval call to provide a value for lastval.

If for some reason you don't need to know that value. but just want some recent value, possibly from another session or even a transaction that was rolled back.

select * from rss_sub_source_id_seq;

Will give you some information about the current state of the sequence without advancing it.

data=> select * rss_sub_source_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
     676535 |      32 | t
0

invoke nextval first:

select nextval('rss_sub_source_id_seq') 

then can using this comamnd:

select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id')) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.