A sequence in Postgres is nothing more than a database object that generates an ordered list of integers. In Postgres, sequences can be created using a “CREATE SEQUENCE” command. However, when a sequence is no longer needed it can be dropped/removed from the database using a “DROP SEQUENCE” command. Sequences that are linked to a table's column will be automatically removed/dropped when the table or even if that particular column is removed.
This post will explain how to drop single or multiple sequences using suitable examples.
How to Drop a Sequence in PostgreSQL?
To drop single or multiple sequences in Postgres, use the DROP SEQUENCE command. To remove a single Postgres sequence, execute the “DROP SEQUENCE” command followed by the sequence name to be dropped:
DROP SEQUENCE [IF EXISTS] seq_name [CASCADE | RESTRICT];
Here in the above syntax:
- The “IF EXISTS”, “CASCADE” and “RESTRICT” are optional features that are used with the DROP SEQUENCE statement to fulfill different tasks.
- For instance, "IF EXISTS" checks if the sequence to be dropped exists, then drops it if it does. While the CASCADE allows the users to delete the database objects that are dependent on a particular sequence.
Execute the “DROP SEQUENCE” command with the comma-separated syntax to drop multiple Postgres sequences:
DROP SEQUENCE seq_name_1, seq_name_2, …;
Let’s head towards examples for a profound understanding of the “DROP SEQUENCE” command.
Example 1: How to Drop a Single Sequence in Postgres?
Before dropping a sequence, first, let’s check the list of available sequences using the following “\ds” command:
\ds
Suppose we want to drop a sequence named “std_seq”, for this, we will execute the DROP SEQUENCE command as follows:
DROP SEQUENCE std_seq;
The output indicates that the selected sequence has been dropped successfully. You can confirm the sequence’s deletion by executing the following command:
\ds;
The output snippet proves that the “std_seq” doesn’t exist in the list of available sequences.
Example 2: How to Drop Multiple Sequences in Postgres?
In the following example, we will use the DROP SEQUENCE command to drop more than one sequence:
DROP SEQUENCE example_seqence, example_seqence_1;
Let’s verify the sequences’ deletion by executing the “\ds” command:
\ds;
The output demonstrates that the selected sequences have been removed from the list of available sequences.
Conclusion
To drop single or multiple sequences in Postgres, use the DROP SEQUENCE command. To remove a single Postgres sequence, execute the “DROP SEQUENCE” command followed by the sequence name to be dropped. Execute the stated command with the comma-separated syntax to drop multiple sequences in one go. This post has demonstrated various use cases of the DROP SEQUENCE command in PostgreSQL.
