In PostgreSQL, the sequences are used to auto-generate a series of unique integers. A common use case of sequences is creating auto-increment primary keys or table columns. While working with sequences a need of resetting or changing the sequence’s value may arise. To deal with such a situation the ALTER SEQUENCE command is executed with an optional “WITH RESTART” clause.
This blog will demonstrate how to reset a sequence in PostgreSQL using practical examples.
How to RESET a SEQUENCE in Postgres?
In PostgreSQL, the “ALTER SEQUENCE RESTART WITH” command allows us to change or reset the value of any sequence. To do that, the below syntax is used in Postgres:
ALTER SEQUENCE seq_name RESTART WITH new_value;
The above query will reset the sequence according to the value specified in place of the “new_value”.
Example: Resetting Sequence Value
Follow the given stepwise instructions to reset a sequence in Postgres:
Step 1: List Sequences
Let’s utilize the “\ds” command to get the list of available sequences:
\ds;
Suppose we want to alter the “product_details_pro_id_seq” sequence.
Step 2: Fetch Sequence Data
Let’s fetch the sequence data using the following “SELECT” command:
SELECT * FROM product_details_pro_id_seq;
The above snippet indicates that the sequence current value is “14”.
Step 3: Reset Sequence
Now utilize the ALTER SEQUENCE command with the RESTART WITH clause to reset the value of the sequence:
ALTER SEQUENCE product_details_pro_id_seq RESTART WITH 100;
The ALTER SEQUENCE command is executed successfully.
Step 4: Verify Sequence Modification
Execute the “SELECT *” command to fetch the sequence data:
SELECT * FROM product_details_pro_id_seq;
The sequence value has been successfully reset to 100.
Conclusion
In PostgreSQL, the ALTER SEQUENCE command is used with the RESTART WITH clause to reset the value of a specific sequence. Once the sequence is altered, you can run the “SELECT *” command followed by the sequence name to verify the altered parameters. This blog post has explained how to reset or restart a sequence in PostgreSQL using the ALTER SEQUENCE command.
