postgresql sequence getting max_value

Question

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

postgres 10?.. I assume

Vao Tsun
– Vao Tsun

2017-11-29 11:24:47 +00:00
Commented Nov 29, 2017 at 11:24 — Vao Tsun
– Vao Tsun, Commented Nov 29, 2017 at 11:24

Vao Tsun · Accepted Answer · 2017-11-29 11:35:40Z

9

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.

edited Nov 29, 2017 at 11:35

answered Nov 29, 2017 at 11:26

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ccleve Over a year ago

select * from pg_sequences, with an 's', not pg_sequence. You'll get a lot more info.

Frederic Close · Accepted Answer · 2019-09-05 07:30:47Z

4

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

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

answered Sep 5, 2019 at 7:30

Frederic Close

9,6657 gold badges58 silver badges69 bronze badges

Comments

krithikaGopalakrishnan · Accepted Answer · 2017-11-30 07:11:13Z

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

answered Nov 30, 2017 at 7:11

krithikaGopalakrishnan

1,33513 silver badges33 bronze badges

Comments

Rihad · Accepted Answer · 2022-04-29 16:57:49Z

0

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

answered Apr 29, 2022 at 16:57

Rihad

1031 gold badge2 silver badges7 bronze badges

Comments

gil.fernandes · Accepted Answer · 2024-02-20 16:27:04Z

0

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.

answered Feb 20, 2024 at 16:27

gil.fernandes

14.8k7 gold badges78 silver badges87 bronze badges

Collectives™ on Stack Overflow

postgresql sequence getting max_value

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related