postgresql sequence getting max_value
NickName:Kax A Ask DateTime:2017-11-29T19:22:34

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

Copyright Notice:Content Author:「Kax A」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/47551628/postgresql-sequence-getting-max-value

Answers
Vao Tsun 2017-11-29T11:26:15

t=# create sequence seqtest increment 1 minvalue 0 maxvalue 20;\nCREATE SEQUENCE\nt=# select * from pg_sequence where seqrelid = 'seqtest'::regclass;\n seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle\n----------+----------+----------+--------------+--------+--------+----------+----------\n 16479 | 20 | 0 | 1 | 20 | 0 | 1 | f\n(1 row)\n\n\nPostgres 10 introduced new catalog: https://www.postgresql.org/docs/10/static/catalog-pg-sequence.html\n\nalso:\nhttps://www.postgresql.org/docs/current/static/release-10.html\n\n\n . Move sequences' metadata fields into a new pg_sequence system catalog\n (Peter Eisentraut)\n \n A sequence relation now stores only the fields that can be modified by\n nextval(), that is last_value, log_cnt, and is_called. Other sequence\n properties, such as the starting value and increment, are kept in a\n corresponding row of the pg_sequence catalog.\n",


krithikaGopalakrishnan 2017-11-30T07:11:13

Alternatively, it can be achieved using psql prompt using the command\n\\d \n\npostgres=# \\d seqtest\n Sequence \"public.seqtest\"\n Type | Start | Minimum | Maximum | Increment | Cycles? | Cache \n--------+-------+---------+---------+-----------+---------+-------\n bigint | 0 | 0 | 20 | 1 | no | 1\n",


Frederic Close 2019-09-05T07:30:47

select min_value, max_value from pg_sequences where sequencename = 'seqtest';\n\n\nhttps://www.postgresql.org/docs/10/view-pg-sequences.html",


More about “postgresql sequence getting max_value” related questions

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 ...

Show Detail

Update ID if Sequence MAX_VALUE reached

I am using the following TRIGGER to Insert in my TEST Table: create or replace TRIGGER TRG_CYCLE BEFORE INSERT ON TEST_CYCLE FOR EACH ROW BEGIN IF :NEW.LOGID IS NULL THEN SELECT SEQ_CYCLE.nex...

Show Detail

Django, accessing PostgreSQL sequence

In a Django application I need to create an order number which looks like: yyyymmddnnnn in which yyyy=year, mm=month, dd=day and nnnn is a number between 1 and 9999. I thought I could use a Postgr...

Show Detail

Postgresql query to fetch sequence metadata

I'm looking for query which will give the complete metadata of sequence. When I tried in console with the below query I got the metadata. \d+ phonebook_id_seq Sequence "public.

Show Detail

Sequence in postgresql

Converting below SQL Server procedures and tables to store and generate sequence to postgresql. Can anyone guide how to do this in Postgres (via table and this function) and not via sequence or n...

Show Detail

Sequence with no cache for Postgresql

I'm trying to migrate from Oracle to PostgreSql. I have this script for Oracle: create sequence MY_SEQ nocache; And nocache is not a valid for PostgreSQl. Any suggestion for postgresql for the

Show Detail

How to view sequence details with a SQL

As you know, we can view the details of a sequence by using '\d' command. I would like to know if there is a way to get these information with a SQL query so that I can view the details for all seq...

Show Detail

Spring boot application not able to find sequence in AWS Postgresql

In existing spring boot application I have created new entity and for the primary key for this entity I have use annotation @GeneratedValue. Based on entity and annotation I have created required t...

Show Detail

Create Sequence in PostgreSQL

I am trying to create a new sequence in PostgreSQL with the start value being the max of all available ids in all the tables using the following query: CREATE SEQUENCE idschema.global_id_sequence

Show Detail

Error Creating Sequence In PostgreSQL

I have following PostgreSQL create script CREATE SEQUENCE seq_tbl_ct_yr2_id START (select max(ct_tran_id)+1 tranid from tbl_ct); this doestnt create sequence arising folloeing error: ERROR:

Show Detail