In Postgres, sequences are used to create the auto-incremented integers. A sequence can be created with various parameters using the CREATE SEQUENCE statement. These parameters include the START, MINVALUE, INCREMENT, CYCLE, and so on. All these parameters define the behavior of a sequence and are used/set at the time of the sequence creation. However, Postgres allows us to modify the parameters of an already created sequence using the ALTER SEQUENCE statement.
This post will explain the use of the ALTER SEQUENCE statement in PostgreSQL using relevant examples.
How to Use ALTER SEQUENCE Command in PostgreSQL?
In PostgreSQL, the ALTER SEQUENCE command allows us to alter the defined parameters of an already existing sequence. To use the ALTER SEQUENCE statement in Postgres, users are advised to follow the provided syntax:
ALTER SEQUENCE seq_name parameter_name value;
Example 1: Alter Sequence Maximum Value
Let’s use the “\ds” command to get the list of available sequences:
\ds;
Suppose we want to alter the “example_1” sequence. For this purpose, first, we will see the structure of the selected sequence using the following command:
\d example_1;
Suppose we want to change the value of the maximum parameter from “60” to “100”. For this, we will use the ALTER SEQUENCE command as follows:
ALTER SEQUENCE example_1 MAXVALUE 100;
Let’s describe the structure of the selected sequence using the following command:
\d example_1;
The output shows that the selected sequence has been altered successfully.
Example 2: Alter Sequence Start Value
Let’s execute the ALTER SEQUENCE command one more time to modify the sequence start value from “40” to “10”:
ALTER SEQUENCE example_1 START 10;
Let’s confirm the sequence altered parameter using the following command:
\d example_1;
Example 3: Restart a Sequence
Users can restart a sequence using the ALTER SEQUENCE statement. To do that, first, let’s fetch the sequence data using the following command:
SELECT * FROM author_info_author_id_seq;
Now utilize the ALTER SEQUENCE statement with the RESTART parameter to set the restart value of the sequence:
ALTER SEQUENCE author_info_author_id_seq RESTART WITH 1;
Now run the “SELECT *” command to fetch the sequence data:
SELECT * FROM author_info_author_id_seq;
That was all about the use of the “ALTER SEQUENCE” statement in PostgreSQL.
Conclusion
In PostgreSQL, the ALTER SEQUENCE command allows us to alter the defined parameters of an already existing sequence. Using the ALTER SEQUENCE statement, users can modify the value of any sequence parameter such as START, MINVALUE, INCREMENT, CYCLE, and so on. This article has provided a thorough guide on how to use the ALTER SEQUENCE statement in Postgres.
