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
INCREMENT BY 2;
``````

### Descending sequence

Let’s create a sequence that is descending

``````CREATE SEQUENCE descendingsequence
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