How to use the ALTER SEQUENCE Command In SQL
The ALTER SEQUENCE command in PostgreSQL is used to modify the properties of a sequence. Sequences are database objects that generate unique sequential numbers, typically used as primary key values.
Syntax:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ START [ WITH ] start ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ CACHE ]
[ CYCLE | NO CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ];
- name: is the name of the sequence object to be altered.
- increment: is the increment value for the sequence. If omitted, the increment value is 1.
- start: is the start value for the sequence. If omitted, the start value is the current value of the sequence.
- minvalue and maxvalue: specify the minimum and maximum values for the sequence, respectively. If omitted, the minimum and maximum values are determined by the integer, bigint, or smallint data type of the sequence.
- cache: specifies the cache size for the sequence. If omitted, the cache size is set to the default value.
- cycle: specifies that the sequence should cycle after reaching its maximum value. If omitted, the sequence does not cycle.
- owned by: specifies the table and column that own the sequence. If omitted, the sequence is owned by no table or column
Example
The following query reset the sequence to start from the next value after the maximum course_id. Assuming the sequence name is courses_course_id_seq and returns the result as before:
ALTER SEQUENCE courses_course_id_seq RESTART WITH 5;
The above query, resets the auto-increment sequence for the course_id column in the courses table to start from 5,.
Output:
How to Reset the Primary Key Sequence ID in PostgreSQL?
PostgreSQL is a powerful, open-source, object-relational database management system (DBMS) developed by a community of contributors. primary key in PostgreSQL is a unique identifier for each row in a table. It is a column or a combination of columns that ensures the integrity of the data in the table. In this article, we will explore how to Reset the Primary Key Sequence ID with the help of different approaches & we will also see examples of each approach.