CODEWITHSUNDEEP
sqlpostgresqlsequence
Kax A
Kax A

Reputation: 43

postgresql sequence getting max_value

How to get max_value and min_value Postgres sequence?

I created the sequence using this statement

create sequence seqtest increment 1 minvalue 0 maxvalue 20;

I tried this query select max_value from seqtest gives error 

ERROR:  column "max_value" does not exist
LINE 1: select max_value from seqtest;
HINT:  Perhaps you meant to reference the column "seqtest.last_value".

Output of select * from seqtest 

test=# select * from seqtest;

-[ RECORD 1 ]-
last_value | 0
log_cnt    | 0
is_called  | f

Upvotes: 4

Views: 14318

Answers (5)

gil.fernandes
gil.fernandes

Reputation: 14611

If you are using the psql tool you can use:

\d <sequence_name>

Example:

my_db=# \d hibernate_sequence
     Sequence "public.hibernate_sequence"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | hibernate_sequence
 last_value    | bigint  | 6086596
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 32
 is_cycled     | boolean | f
 is_called     | boolean | t

The max value is in the response.

Upvotes: 0

Rihad
Rihad

Reputation: 103

But this query shows seqmax=9223372036854775807 (2^63-1) for any kind of sequence somehow.

Upvotes: 0

Frederic Close
Frederic Close

Reputation: 9639

select min_value, max_value from pg_sequences where sequencename = 'seqtest';

https://www.postgresql.org/docs/10/view-pg-sequences.html

Upvotes: 3

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

Alternatively, it can be achieved using psql prompt using the command \d 

postgres=# \d seqtest
                    Sequence "public.seqtest"
  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache 
--------+-------+---------+---------+-----------+---------+-------
 bigint |     0 |       0 |      20 |         1 | no      |     1

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51456

t=# create sequence seqtest increment 1 minvalue 0 maxvalue 20;
CREATE SEQUENCE
t=# select * from pg_sequence where seqrelid = 'seqtest'::regclass;
 seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle
----------+----------+----------+--------------+--------+--------+----------+----------
    16479 |       20 |        0 |            1 |     20 |      0 |        1 | f
(1 row)

Postgres 10 introduced new catalog: https://www.postgresql.org/docs/10/static/catalog-pg-sequence.html

also: https://www.postgresql.org/docs/current/static/release-10.html

. Move sequences' metadata fields into a new pg_sequence system catalog (Peter Eisentraut)

A sequence relation now stores only the fields that can be modified by nextval(), that is last_value, log_cnt, and is_called. Other sequence properties, such as the starting value and increment, are kept in a corresponding row of the pg_sequence catalog.

Upvotes: 7

Related Questions