August 2, 2023
Summary: In this tutorial, you will learn about the PostgreSQL sequences and how to use a sequence object to generate a sequence of numbers.
Table of Contents
By definition, a sequence is an ordered list of integers. The orders of numbers in the sequence are important. For example, {1,2,3,4,5}
and {5,4,3,2,1}
are entirely different sequences.
A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification.
To create a sequence in PostgreSQL, you use the CREATE SEQUENCE
statement.
Introduction to PostgreSQL CREATE SEQUENCE
statement
The following illustrates the syntax of the CREATE SEQUENCE
statement:
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 } ]
sequence_name
Specify the name of the sequence after the CREATE SEQUENCE
clause. The IF NOT EXISTS
conditionally creates a new sequence only if it does not exist.
The sequence name must be distinct from any other sequences, tables, indexes, views, or foreign tables in the same schema.
[ AS { SMALLINT | INT | BIGINT } ]
Specify the data type of the sequence. The valid data type is SMALLINT
, INT, and BIGINT
. The default data type is BIGINT
if you skip it.
The data type of the sequence which determines the sequence’s minimum and maximum values.
[ INCREMENT [ BY ] increment ]
The increment
specifies which value to be added to the current sequence value to create new value.
A positive number will make an ascending sequence while a negative number will form a descending sequence.
The default increment value is 1.
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
Define the minimum value and maximum value of the sequence. If you use NO MINVALUE
and NO MAXVALUE
, the sequence will use the default value.
For an ascending sequence, the default maximum value is the maximum value of the data type of the sequence and the default minimum value is 1.
In case of a descending sequence, the default maximum value is -1 and the default minimum value is the minimum value of the data type of the sequence.
[ START [ WITH ] start ]
The START
clause specifies the starting value of the sequence.
The default starting value is minvalue
for ascending sequences and maxvalue
for descending ones.
cache
The CACHE
determines how many sequence numbers are preallocated and stored in memory for faster access. One value can be generated at a time.
By default, the sequence generates one value at a time i.e., no cache.
CYCLE | NO CYCLE
The CYCLE
allows you to restart the value if the limit is reached. The next number will be the minimum value for the ascending sequence and maximum value for the descending sequence.
If you use NO CYCLE
, when the limit is reached, attempting to get the next value will result in an error.
The NO CYCLE
is the default if you don’t explicitly specify CYCLE
or NO CYCLE
.
OWNED BY table_name.column_name
The OWNED BY
clause allows you to associate the table column with the sequence so that when you drop the column or table, PostgreSQL will automatically drop the associated sequence.
Note that when you use the SERIAL
pseudo-type for a column of a table, behind the scenes, PostgreSQL automatically creates a sequence associated with the column.
PostgreSQL CREATE SEQUENCE examples
Let’s take some examples of creating sequences to get a better understanding.
1) Creating an ascending sequence example
This statement uses the CREATE SEQUENCE
statement to create a new ascending sequence starting from 100 with an increment of 5:
CREATE SEQUENCE mysequence
INCREMENT 5
START 100;
To get the next value from the sequence to you use the nextval()
function:
SELECT nextval('mysequence');
If you execute the statement again, you will get the next value from the sequence:
SELECT nextval('mysequence');
2) Creating a descending sequence example
The following statement creates a descending sequence from 3 to 1 with the cycle option:
CREATE SEQUENCE three
INCREMENT -1
MINVALUE 1
MAXVALUE 3
START 3
CYCLE;
When you execute the following statement multiple times, you will see the number starting from 3, 2, 1 and back to 3, 2, 1 and so on:
SELECT nextval('three');
3) Creating a sequence associated with a table column
First, create a new table named order_details
:
CREATE TABLE order_details(
order_id SERIAL,
item_id INT NOT NULL,
item_text VARCHAR NOT NULL,
price DEC(10,2) NOT NULL,
PRIMARY KEY(order_id, item_id)
);
Second, create a new sequence associated with the item_id
column of the order_details
table:
CREATE SEQUENCE order_item_id
START 10
INCREMENT 10
MINVALUE 10
OWNED BY order_details.item_id;
Third, insert three order line items into the order_details
table:
INSERT INTO
order_details(order_id, item_id, item_text, price)
VALUES
(100, nextval('order_item_id'),'DVD Player',100),
(100, nextval('order_item_id'),'Android TV',550),
(100, nextval('order_item_id'),'Speaker',250);
In this statement, we used the nextval()
function to fetch item id value from the order_item_id
sequence.
Fourth, query data from the order_details
table:
SELECT
order_id,
item_id,
item_text,
price
FROM
order_details;
Listing all sequences in a database
To list all sequences in the current database, you use the following query:
SELECT
relname sequence_name
FROM
pg_class
WHERE
relkind = 'S';
Deleting sequences
If a sequence is associated with a table column, it will be automatically dropped once the table column is removed or the table is dropped.
You can also remove a sequence manually using the DROP SEQUENCE
statement:
DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...]
[ CASCADE | RESTRICT ];
In this syntax:
- First, specify the name of the sequence which you want to drop. The
IF EXISTS
option conditionally deletes the sequence if it exists. In case you want to drop multiple sequences at once, you can use a list of comma-separated sequence names. - Then, use the
CASCADE
option if you want to recursively drops objects that depend on the sequence, and objects that depend on the dependent objects and so on.
PostgreSQL DROP SEQUENCE
statement examples
This statement drops the table order_details
. Since the sequence order_item_id
associates with the item_id
of the order_details
, it is also dropped automatically:
DROP TABLE order_details;
In this tutorial, you have learned about PostgreSQL sequences and how to use a sequence object to generate a list of sequences.