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:

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ AS { SMALLINT | INT | BIGINT } ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] 
    [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] 
    [ CACHE cache ] 
    [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

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

Parameter	Description
sequence_name	The sequence_name is different from any other sequences, indexes, tables, views, or foreign tables in a similar schema. We can define the sequence name subsequently the CREATE SEQUENCE And the IF NOT EXISTS condition tentatively generates a new sequence only if it does not exist.
[ AS { SMALLINT \| INT \| BIGINT } ]	The data type of the sequence which regulates the sequence's maximum and minimum values. We can define the data type of the sequence, where the supported data type is INT, BIGINT, and SMALLINT. If we forgot to mention the data type, it takes it as BIGINT because it is a default data type for Sequence.
[ INCREMENT [ BY ] increment ]	The increment describes the value, which has to be added to the existing sequence value to generate a new value and by default value is 1. Here, a positive (+) number will produce an ascending sequence, whereas a negative (-) number will generate a descending sequence.
[ MINVALUE minvalue \| NO MINVALUE ] [ MAXVALUE maxvalue \| NO MAXVALUE ]	If we use the NO MINVALUE and NO MAXVALUE, the sequence will take the default value. For an ascending sequence,the maximum default value is the maximum value of the Sequence data type, and the default minimum value is 1. And for a descending sequence, the maximum default value is -1, and the default minimum value is the minimum value of the data type of the Sequence.
[ START [ WITH ] start ]	The STARTclause is used to define the starting value of the sequence. And the default initial value is Max value for descending ones and Min value for ascending sequences.
cache	One value can be created at a time, and by default, the sequence creates one value at a time that is no cache. The CACHE parameter is used to specify the total sequence numbers pre-allocated and stored in memory for earlier access.
CYCLE \| NO CYCLE	The CYCLE parameter allows us to resume the value if the limit is reached. The following number will be the maximum value for the descending sequence and the minimum value for the ascending sequence. If we use NO CYCLE, when the limit is reached, or we are trying to get the next value, it will raise an error in the output. The NO CYCLE is the default if we do not define the CYCLE or NO CYCLE.
OWNED BY table_name.column_name	At last, the OWNED BY parameter is used to link the table column with the sequence. Therefore, PostgreSQL will automatically drop the related sequence; if we drop the table or the column.

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.

CREATE SEQUENCE jtpsequence
INCREMENT 3
START 20;

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:

CREATE SEQUENCE five
INCREMENT -1
MINVALUE 1 
MAXVALUE 5
START 5
CYCLE;

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.

CREATE TABLE Purchase_details(
    Purchase_id SERIAL,
    Module_id INT NOT NULL,
    Module_text VARCHAR NOT NULL,
    Cost DEC(10,2) NOT NULL,
    PRIMARY KEY(Purchase_id, Module_id)
);

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:

CREATE SEQUENCE Purchase_module_id
START 5
INCREMENT 5
MINVALUE 5
OWNED BY Purchase_details.Module_id;

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.

INSERT INTO 
 Purchase_details(Purchase_id, Module_id, Module_text, cost)
VALUES
    (150, nextval('Purchase_module_id'),'Iphone11 max pro',500),
    (150, nextval('Purchase_module_id'),'Smart LED Tv',650),
    (150, nextval('Purchase_module_id'),'Home theatre',200);

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:

SELECT Purchase_id, Module_id, Module_text, Cost
FROM Purchase_details;

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:

SELECT relname sequence_name
FROM  pg_class 
WHERE  relkind = 'S';

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:

DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...] 
[ CASCADE | RESTRICT ];

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

Parameters	Description
Sequence_name	It is used to define the name of the sequence, which we want to delete.
If EXISTS	We can use a list of comma-separated sequence names if we need to remove several sequences at a time. And the IF EXISTS parameter temporarily removes the sequence if it present.
CASCADE	if we want to delete objects based on the Sequence recursively, we can use the CASCADE option.

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