The PostgreSQL database offers various features and one of them is PostgreSQL Sequence. A PostgreSQL sequence is a database-related object that is used to create a sequence of values starting from a specific value. It usually takes a starting point, an increment value, the minimum value it can have, and the maximum value it can approach. Thus, we decided to tell you about using a PostgreSQL sequence in a database and how to delete an already existing sequence in various ways. So let’s get started.
Delete sequence via pgAdmin
Let’s see how to create a sequence and delete it using the pgAdmin user interface of a PostgreSQL database. To do this, we need to launch pgAdmin from the search bar of our Windows 10. Add a password for the server and database, such as aqsayasin to make pgAdmin usable. pgAdmin is now running on our system. Explore the databases you have. We will work in the “aqsayasin” database as shown in the figure.
In schematics, you should look into the “Sequence” option as shown in the picture. If there is any sequence in your database, it will show the list. Since there is no sequence in our database right now, so it is empty.
Let’s create a new sequence in our “aqsayasin” database using pgAdmin. Right-click the Sequence parameter to explore it. The dialog shown below will appear. Hover over the Create option to expand it. Click on the “Sequence” option to quickly create a new sequence.
The screen below will open in your pgAdmin application to create the sequence. In the text field before the “Name” option, write the name of the sequence you want to create. We called it “test”. You must select the owner of your sequence, i.e. username/database. In our case, this is “aksayasin”. Go to the “Definition” section.
Here we need to create the basic settings for our sequence. You must set each value for each option. For example, we want to increase the value of a sequence by 5, and the start of the sequence is set to 5. This means that wherever this sequence is going to be used, it will always start at 5. Each time the sequence is called, it will be incremented by 5. We must set the minimum and the maximum value for the generated sequence so that it does not exceed both ends. In the SQL section, you can see a query to create a sequence in PostgreSQL. Click on the “Save” button to finalize the creation of the “test” sequence.
Update the “Sequences over Tables” parameter in the “aqsayasin” database. You will see that the “test” sequence you just created is listed there and ready to use.
To see the very first value of the “test” sequence, you can use the SELECT command in the query editor area as shown below, followed by the name of the sequence. The first value of the sequence is 5 according to the output.
To increment the first value of the “test” sequence by 5, you need to call the sequence with the “nextval” function using the SELECT statement as shown. An increased value of 10 is displayed.
Using the nextval() function will increment the value of the sequence each time. We called it 5 times and got the result 35.
Calling the “test” sequence 5 more times with the nextval() function displays the value “60”, which is also the maximum value for our sequence. After that, it will not increase.
Using the nextval() function in the SELECT query again leads us to an error explaining that the test sequence has reached its maximum.
To delete or discard the “test” sequence, you need to right-click on it to see the options available. In the dialog box you will find the options “Remove/Remove” and “Remove Cascade”. Both can be used in different ways. The Delete/Remove option will only delete the sequence, while the Cascade Delete option will also delete objects that depend on it.
When using the Uninstall / Uninstall option, we will get the below dialog box on our screen. Click “yes” to continue deleting this “test” sequence.
When using the “Cascading Delete” option, the below dialog box will appear to make sure you want to delete objects associated with the sequence along with the particular sequence. Click on the “Yes” button to continue.
Deleting a sequence via PostgreSQL wrapper
To delete a sequence, it must be in your current database. Log in from the PostgreSQL shell by adding your local hostname, database name, port number, username, and password. In the query, the scope uses the CREATE SEQUENCE command to create a sequence named “Num”. For a simple illustration, we have only added an increment value and a start value for this sequence.
To remove the “Number” sequence, use the DROP SEQUENCE command. You will no longer find your sequence in the database.
Let’s look at the bigger picture. We’ll start this example by creating a new Makeup table using the CREATE TABLE command. There are 3 columns in the table. The SELECT query shows that the table is empty.
You need to create a “test” sequence using the CREATE SEQUENCE command. We added an initial value, an increment value, a minimum value, a maximum value, and a table of owners of this sequence, i.e. Makeup. Thus, the “Makeup” table depends on the “test” sequence.
Substitute the values into the Makeup table. In the VALUES section of the INSERT INTO command, we used the nextval() function to use a test sequence to add values to the table. A total of 5 entries were added.
The SELECT query can display newly added Makeup table data. You can see that the “price” column contains values starting at 1000 and increasing by 1000 on each row using the “test” sequence.
Add 5 more entries in the same way, for example, using the INSERT INTO command and the nextval() function.
A total of 10 records were found using the SELECT statement. We know that the maximum value defined for the “test” sequence is “10000” and it has already been reached in the 10th row of the price column. This means we won’t be able to add more entries.
When using the INSERT INTO command to add more records to the Makeup table, we encountered an error as shown below. It shows that the sequence has already reached its maximum value, i.e. 10,000.
To remove the “test” sequence, we need to use the DROP SEQUENCE command with the RESTRICT command.
When you try to add data to the “makeup” table again using the test sequence in the nextval() function, it won’t let you because the “test” sequence is gone.
This article demonstrates how to use a PostgreSQL sequence and how to remove it from a PostgreSQL database when running on Windows 10. We tried to create and remove a sequence using the PostgreSQL GUI, i.e. using pgAdmin and the PostgreSQL shell separately. The examples clearly show the various methods for deleting/removing a sequence individually and together with its associated objects.