Introduction to PostgreSQL sequences

In PostgreSQL, a sequence is a database object that allows you to generate a sequence of unique integers.

Typically, you use a sequence to generate a unique identifier for a primary key in a table. Additionally, you can use a sequence to generate unique numbers across tables.

To create a new sequence, you use theCREATE SEQUENCE statement.

PostgreSQL CREATE SEQUENCE statement

The following illustrates the syntax of theCREATE 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 theCREATE SEQUENCE clause. TheIF 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 thedata type of the sequence. The valid data type isSMALLINT,INT, andBIGINT. The default data type isBIGINT if you skip it.

The data type of the sequence which determines the sequence’s minimum and maximum values.

[ INCREMENT [ BY ] increment ]

Theincrement specifies which value to add to the current sequence value.

A positive number will make an ascending sequence whereas 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 useNO MINVALUE andNO 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 the 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 ]

TheSTART clause specifies the starting value of the sequence.

The default starting value isminvalue for ascending sequences andmaxvalue for descending ones.

cache

TheCACHE 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

TheCYCLE allows you to restart the value if the limit is reached. The next number will be the minimum value for the ascending sequence and the maximum value for the descending sequence.

If you useNO CYCLE, when the limit is reached, attempting to get the next value will result in an error.

TheNO CYCLE is the default if you don’t explicitly specifyCYCLE orNO CYCLE.

OWNED BY table_name.column_name

TheOWNED 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 theSERIAL 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 theCREATE SEQUENCE statement to create a new ascending sequence starting from 100 with an increment of 5:

CREATE SEQUENCE mysequenceINCREMENT5START 100;

To get the next value from the sequence, you use thenextval() 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 threeINCREMENT-1MINVALUE1MAXVALUE3START 3CYCLE;

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 namedorder_details:

CREATE TABLE order_details(    order_idSERIAL,    item_idINT NOT NULL,    item_textVARCHAR NOT NULL,    priceDEC(10,2)NOT NULL,    PRIMARY KEY(order_id, item_id));

Second, create a new sequence associated with theitem_id column of theorder_details table:

CREATE SEQUENCE order_item_idSTART 10INCREMENT10MINVALUE10OWNEDBY order_details.item_id;

Third,insert three order line items into theorder_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 thenextval() function to fetch the item id value from theorder_item_id sequence.

Fourth,query data from theorder_details table:

SELECT    order_id,    item_id,    item_text,    priceFROM    order_details;

Listing all sequences in a database

To list all sequences in the current database, you use the following query:

SELECT    relname sequence_nameFROM    pg_classWHERE    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 theDROP 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. TheIF EXISTS option conditionally deletes the sequence if it exists. If you want to drop multiple sequences at once, you can use a list of comma-separated sequence names.
• Then, use theCASCADE option if you want to drop objects that depend on the sequence recursively, objects that depend on the dependent objects, and so on.

PostgreSQL DROP SEQUENCE statement examples

This statement drops the tableorder_details. Since the sequenceorder_item_id associates with theitem_id of theorder_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.

Last updated onFebruary 8, 2024

On this page

• Introduction to PostgreSQL sequences
• PostgreSQL CREATE SEQUENCE statement
• PostgreSQL CREATE SEQUENCE examples
• Listing all sequences in a database
• Deleting sequences