A sequence is is user-defined object that is tied to a schema and creates a numerical sequence using a particular spec.

The order of the numbers in a sequence are very important. For example, {1,2,3} is not the same as {3,2,1}.

Structure of a sequence

CREATE SEQUENCE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ AS { data_type } ]
    [ 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 } ]

Creating a sequence

Ascending sequence

Suppose we want to create a sequence that starts from 2 and increments by 2

CREATE SEQUENCE ex1sequence
START WITH 2
INCREMENT BY 2;

Descending sequence

Let’s create a sequence that is descending

CREATE SEQUENCE descendingsequence
START WITH 10
INCREMENT BY -1
MAX VALUE 10
MIN VALUE 1
CYCLE;

Using sequence with a table

Suppose we have the following table:

CREATE TABLE users
(user_id BIGINT,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100)
);

Now, let’s create a sequence for the above table

CREATE SEQUENCE users_seq
START 1
INCREMENT 1
MINVALUE 1
OWNED BY users.user_id;

Since the sequence has been created, whenever we want to insert data into the table, we can use the nextval() function:

INSERT INTO users (user_id, first_name, last_name, email)
VALUES
    (nextval('users_seq'), 'Tom', 'Smith', '[email protected]');

List all sequences in a database

If we want to list all sequences in a database, we use the query below:

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

Drop a sequence

If we want to drop (delete) a sequence, we use the following command:

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

If a sequence is tied to either a column in a table or a table, it will get dropped either when the column gets dropped if it is tied to it or when the table is dropped if it is tied to it. The command above is to be used if you want to manually drop a sequence.