PostgreSQL

Drop Sequence Postgres

PostgreSQL database comes up with various functionalities and one of them is PostgreSQL Sequence. PostgreSQL sequence is a kind of database-bound object used to create a sequence of values starting from some particular value. It usually takes the starting point, increment value, minimum value it can have, and the maximum value it can approach to. Thus, we have decided to brief you about the use of the PostgreSQL sequence in the database and how to delete an already existing sequence in different ways. So, let’s get started.

Drop Sequence Through pgAdmin

Let’s see how to create a sequence and drop it using the pgAdmin User interface of the PostgreSQL database. For this, we need to start the pgAdmin from the search bar of our Windows 10. Add the password for server and database i.e. aqsayasin to make the pgAdmin useable for us. Now, the pgAdmin is launched on our system. Explore the databases you have. We will be working in the “aqsayasin” database as shown.

Within the Schemas, you have to explore the Sequence option as shown. If there is any sequence in your database, it will show the list. As we have no sequence right now in our database, therefore it’s empty.

Let’s create a new sequence within our database “aqsayasin” using pgAdmin. Right-click on the option Sequence to explore it. The below-shown dialogue will appear. Hover over the “Create” option to expand it. Tap on the “Sequence” option to create a new Sequence quickly.

The below-shown screen will be opened in your pgAdmin application to create a sequence. Within the text-are in front of the “Name” option, write the name of a sequence you want to create. We have been naming it “test”. You have to choose the owner of your sequence i.e. username/database. In our case, it’s “aqsayasin”. Move towards the “Definition” section.

Here comes the main settings of our sequence to be created. You have to set each value for each option. For instance, we want to increment the value of a sequence by 5 and the start of a sequence has been set to 5. This means wherever this sequence is going to be used, it will always be started from 5. On each sequence call, it will be incremented by 5. We have to set the minimum and maximum value for the sequence to be created so that it must not exceed both ends. Within the SQL section, you can see the query to create a sequence in PostgreSQL. Tap on the “Save” button to finally complete the creation of sequence “test”.

Refresh the Sequences above the tables option within the database “aqsayasin”. You will see the “test” sequence that has just been created is listed in it and ready for use.

To see the very first value of the sequence “test”, you can use the SELECT command in the query editor area as shown below followed by the name of a sequence. The sequence first value is 5 as per the output.

To increment the first value of sequence “test” by 5, you need to call the sequence with the “nextval” function using the SELECT instruction as shown. The incremented value 10 is shown.

The use of the nextval() function will increment the value of the sequence each time. We have called it 5 times and got the result 35.

On calling the “test” sequence another 5 times with nextval() function displays the value “60” which is also the maximum value for our sequence. After it, it will not increment.

Using the nextval() function within the SELECT query once again leads us to an error explaining that the test sequence has reached its maximum.

To delete or drop the sequence “test”, you need to right-click on it to see the available options. You will find the “Delete/Drop” and “Drop Cascade” options in the dialogue. Both can be utilized in different ways. The Delete/Drop option will only delete the sequence, while the Drop Cascade option will also remove the objects that are depending on it.

On using the “Drop/Delete” option, we will get the below dialogue box on our screen. Tap on “yes” to continue deleting this “test” sequence.

On using the “Drop Cascade” option, below dialogue will be appeared to make sure that you want to delete the objects related to the sequence along with the specific sequence. Tap on the button “Yes” to continue.

Drop Sequence Through PostgreSQL Shell

To drop the sequence, you must have one in your current database. Login from the PostgreSQL shell by adding the name of your localhost, database name, port number, username, and password. Within the query, the area makes use of CREATE SEQUENCE command to create a sequence named “Num”. For a simple illustration, we have been adding only increment value and start value for this sequence.

To delete the sequence “Num”, use the DROP SEQUENCE command. You will no longer find your sequence in the database.

Let’s take a look at the bigger picture. We will start this example by creating a new table “Makeup” with the CREATE TABLE command. The table contains a total of 3 columns. The SELECT query is here to show that the table is empty.

You need to create a sequence “test” with the CREATE SEQUENCE command. We have been adding the start value, increment value, minimum value, maximum value, and the owner table of this sequence i.e., Makeup. This way, the table “Makeup” depends on the “test” sequence.

Let’s insert values in the table Makeup. Within the VALUES section of the INSERT INTO command, we have been using the nextval() function to make use of the test sequence to add values in the table. A total of 5 records have been added.

The SELECT query can show the newly added data of table Makeup. You can see that the column “price” contains the values starting from 1000 and incrementing by 1000 at each row using the “test” sequence.

Add 5 more records using the same way i.e., INSERT INTO command and nextval( ) function.

A total of 10 records have been found so far using the SELECT instruction. We know that the maximum value defined for the “test” sequence is “10000” and it is already reached at the 10th row of the price column. This means we will not be able to add more records.

On using the INSERT INTO command to add more records in the Makeup table, we have found the error as shown below. It is showing that the sequence has already reached its maximum value i.e., 10,000.

To delete the sequence “test”, we need to use the DROP SEQUENCE command with the RESTRICT command.

When you try to add data into the “makeup” table again using the test sequence in the nextval() function, it will not allow you because the sequence “test” is gone.

Conclusion

This article demonstrates the use of the PostgreSQL sequence and the ways to delete it from the PostgreSQL database while working in Windows 10. We have tried to create and drop the sequence using the PostgreSQL GUI i.e., pgAdmin and PostgreSQL Shell separately. The examples are clearly showing the different methods to drop/delete the sequence alone and along with its related objects.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.