'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
sqlpostgresqlsequence


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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

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

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 krithikaGopalakrisnan
Solution 3 Frederic Close
Solution 4 Rihad

Related Questions