PostgreSQL Sequence

In this section, we are going to understand the working of the PostgreSQL Sequence, examples of PostgreSQL Sequence, and understand how the sequence object is used to create a sequence of numbers, and see the example nextval() function.

And we also see the example of creating an ascending and descending sequence using the CREATE SEQUENCE command and remove the Sequence with the help of the DROP SEQUENCE command.

What is PostgreSQL Sequence?

The Sequence is a generator used to create a progressive number that can help to produce a single primary key automatically and synchronize the keys across various rows or tables.

In PostgreSQL, a sequence is a user-defined schema-bound object which creates a sequence of integers depending on the particular requirement.

In PostgreSQL sequence, the orders of numbers are important. Such as {5,6,7,8,9,10} and {10,9,8,7,6,5} are completely different sequences.

We are using the CREATE SEQUENCE command to generate a sequence in PostgreSQL.

PostgreSQL CREATE SEQUENCE command

• The PostgreSQL CREATE SEQUENCE command is used to generate an original sequence number generator, which also includes generating and setting a new different single-row table with the name.
• The generator will be maintained by the user who questions the statements.
• The PostgreSQL Sequences are built on bigint arithmetic; therefore, the range starts from -9223372036854775808 to 9223372036854775807. And we cannot surpass the range of an eight-byte
• In PostgreSQL, the sequence name must be different from any additional sequence, table, view, index, or foreign table in a similar schema.
• The sequence is created in a particular schema, it is generated in the existing schema if a schema name is given earlier.
• We can use currval, setval, and nextval functions to operate on the sequence once the sequence has been generated.
• A schema name cannot be specified when generating a temporary sequence as the temporary sequences occur in a special schema.

Syntax of PostgreSQL Create Sequence command

The syntax of the PostgreSQL Create Sequence is as follows:

In the above Syntax, we have used the following parameters:

Note: When we are using the SERIAL pseudo-type for a column of a table, in the background, PostgreSQL automatically generates a sequence, which is related to the column.

Examples of PostgreSQL Create Sequence

Let us see different examples to understand how PostgreSQL CREATE SEQUENCE works.

• Example of generating an ascending sequence

In the below example, the following command is used the CREATE SEQUENCE command for generating a new ascending sequence Starts from 20 with an increment of 3.

Output

After executing the above command, we will get the following message window, which displays that the Ascending sequence has been created successfully.

Here, we can also use the nextval() function to get the next value from the sequence.

Output

After implementing the above command, we will get the following output, displaying the next value from the sequence.

And if we implement the above command again, we will get the next value from the sequence:

Output

After executing the above command, we will get the below output, which displays the next value from the sequence.

• Example of generating a descending sequence

In the below example, the following command is used to generate a descending sequence from 5 to 1 with the cycle option:

Output

We will get the following message on executing the above command, which displays that the descending sequence has been created successfully into the Organization database.

When we are implementing the below command several times, we will see the number began from 5,4,3, 2, 1 and back to 5,4,3, 2, 1 and so on:

Output

After executing the above command, we will get the below output, displaying the value from the sequence in descending order.

• Creating a sequence related to a table column

Let us see one sample example to understand how to create a sequence related to a table column.

Step1: Creating a new table

Firstly, we are creating one new table as Purchase_details with the CREATE command's help and inserting some values using the INSERT command.

To create a Purchase_details into an Organization database, we use the CREATE command.

The Purchase_details table contains the various columns, such as Purchase_id, Module_id, Module_text, and Cost, where the Purchase_id and Module_id column is the primary key column.

Output

We will get the following message on executing the above command, which displays that the Purchase_details table has been created successfully into the Organization database.

Step2: Creating a new sequence

After creating the Purchase_details table successfully, we will create a new sequence using the CREATE SEQUENCE command, which is linked with the Module_id column of the Purchase_details table, as shown in the following command:

Output

The new sequence has been created successfully after executing the above command:

Step3: Inserting the Data

After creating the Purchase_details table and a Purchase_module_id sequence successfully, we will insert some values into the Purchase_details table with the INSERT command's help.

The below command is used to insert the various purchase line modules into the Purchase_details table.

Output

After implementing the above command, we will get the following message window, which displays that the three values have been inserted successfully into the Purchase_details table.

Note: In the above command, we have used the nextval() function to retrieve the Module_id value from the Purchase_module_id sequence.

Step4: Retrieving the data

After creating and inserting the Purchase_details table's values, we will use the SELECT command to retrieve the data from the Purchase_details table:

Output

After successfully implementing the above command, we will get the below result, which displays that the PostgreSQL returns the data present in the Purchase_details table:

• Listing all sequences in a database

In the following command, we are listing all sequences present in the existing database:

Output

On implementing the above command, we will get the following result, which displays listing all the sequence in the Organization database:

• Removing sequences

It will be automatically removed once the table is dropped, or the table's column is deleted if a sequence is connected with a table column.

Manually, we can use the DROP SEQUENCE command for deleting a sequence.

Syntax of removing the PostgreSQL sequence

The Syntax for removing the PostgreSQL sequence is as follows:

In the above syntax, we have used the following parameters:

Example of PostgreSQL DROP SEQUENCE command

To drop the Purchase_details table, we have used the DROP TABLE command; meanwhile, the Sequence Purchase_module_id is connected with the Module_id of the Purchase_details.

Therefore, it is also removed repeatedly, as we can see in the below command:

Output

After executing the above command, we will get the below message window, which displays that the Purchase_details has been removed successfully.

Overview

In the PostgreSQL Sequence section, we have learned the following topics:

• The PostgreSQL Sequence is used as a Sequence object for creating the list of sequences.
• We have used the CREATE SEQUENCE command to create a new sequence number
• In this section, we also understand how to create an ascending and descending sequence with the CREATE SEQUENCE
• We used the nextval() functions for retrieving the next value from the sequence.
• We also see the example of dropping the sequence with the DROP SEQUENCE / DROP TABLE