PostgreSQL – CREATE SEQUENCE

A sequence in PostgreSQL is a user-defined schema-bound object that yields a sequence of integers based on a specified specification. The CREATE SEQUENCE statement is used to create sequences in PostgreSQL.

Syntax:
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 } ]

Now let’s analyze the above syntax:

• First, set 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.
• Second, specify the data type of the sequence. The valid data type are SMALLINT, INT, and BIGINT. The default data type is BIGINT if you skip it. It is used to determines the sequence’s minimum and maximum values.
• 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.
• Then, we define the minimum value and maximum value of the sequence. If you use NO MINVALUE or 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 whereas 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.
• The START clause specifies the starting value of the sequence.
• 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.
• 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.

Now let’s jump into some examples.

Example 1:
In this example, we will use the CREATE SEQUENCE statement to create a new ascending sequence starting from 10 with an increment of 5:

CREATE SEQUENCE mysequence
INCREMENT 5
START 10;

To get the next value from the sequence to you use the nextval() function:

SELECT nextval('mysequence');

It will result in the below output:

Now if we repeat the nexval() function we get the incremented value.

Output:

Example 2:
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');

Output:

Example 3:
In this example we will create a sequence associated with a table column using the statement below:

CREATE TABLE order_details(
    order_id SERIAL,
    item_id INT NOT NULL,
    product_id INT,
    product_name TEXT 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, product_name, 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);

Fourth, query data from the order_details table:

SELECT
    order_id,
    item_id,
    product_name,
    price
FROM
    order_details;

Output: