PostgreSQL Sequence

In PostgreSQL, the sequence is the schema object that generates a sequence of numbers in ascending or descending order. The sequence is not associated with any table, but it can be used to populate data in the primary key or unique columns of a table.

CREATE SEQUENCE [ IF NOT EXISTS ] 
    [ AS  ]
    [ START [ WITH ] start_value ] 
    [ INCREMENT [ BY ] increment_value ]
    [ MINVALUE minvalue | NO MINVALUE ] 
    [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ size ] } | { NO CACHE } ];
    [ OWNED BY { table_name.column_name | NONE } ]

In the above syntax,

• After CREATE SEQUENCE you can specify the optional IF NOT EXISTS clause, which will create a sequence only if it does not exist. If a sequence already exists, Postgres will only give a warning instead of an error and skip creating a new sequence.
• The sequence_name is the unique name of a sequence that you want to create.
• Integer_type: A sequence is defined with any of the integer types as INT, SMALLINT, BIGINT, and NUMERIC data type.
• start_value: The first value in the sequence.
• increment_value: This is the interval between two consecutive sequence values. If the increment value is negative, then the sequence is a decreasing sequence else it is ascending. The default increment value is 1. The Increment cannot be 0.
• MINVALUE: This specifies the lower bound for a sequence. If not specified, it defaults to the minimum value of the data type of the sequence.
• MAXVALUE: Specify the upper bound for the sequence. It defaults to the maximum value of the data type of the sequence.
• CYCLE: Specifies whether the sequence object should restart from the minimum value (maximum value for descending sequence) or raise an exception when the minimum (or maximum) value is reached. NO CYCLE is the default value. Cycling will restart the sequence from the minimum or maximum value and not from the start value.
• CACHE [ size ]: Improves performance for applications using sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. PostgreSQL pre-allocates the number of sequence numbers specified by the CACHE.
• OWNED BY: Allows you to associate the column of a table with a sequence, so that when you drop the column or table, PostgreSQL will drop the associated sequence automatically.

The following creates a new sequence with the default starting value 1 and increment value 1.

CREATE SEQUENCE myfirstsequence

The above statement creates a sequence named myfirstsequence. It will be created in Schemas -> public -> Sequences node in pgAdmin, as shown below.

The following table lists the sequence manipulation functions:

The following demonstrates the use of sequence manipulation functions.

SELECT currval('myfirstsequence') – error; cannot use currval() if nextval() never called
SELECT nextval('myfirstsequence') –- returns 1 
SELECT currval('myfirstsequence') -- returns 1

SELECT nextval('myfirstsequence') –- returns 2
SELECT lastval() – returns 2

SELECT setval('myfirstsequence', 10, false)
SELECT nextval('myfirstsequence')  -- returns 10

SELECT setval('myfirstsequence', 10, TRUE)
SELECT nextval('myfirstsequence') –- returns 11

SELECT lastval() -- returns 11

Let's create an ascending sequence that starts from 10 and increments by 2.

CREATE SEQUENCE mysequence
START WITH 10
INCREMENT BY 5;

Now get next value from sequence using nextval() function.

SELECT nextval('mysequence')  -- returns 10
SELECT nextval(' mysequence ')  -- returns 15
SELECT nextval('mysequence')  -- returns 20

Descending Sequence

The following creates a descending sequence.

CREATE SEQUENCE descsequence
START WITH 5
INCREMENT BY -1
MAXVALUE 5
MINVALUE 1
CYCLE;

The above will create a sequence that starts with 5 and decrease the value by 1 on each nextval() call until 1. The CYCLE indicates that if the current value reaches the MINVALUE then start with 5 again.

When you execute the nextval() function multiple times, then it will return values starting from 5,4,3, 2, 1, and again 5,4,3, 2, 1, and so on.

SELECT nextval('descsequence'); --returns 5
SELECT nextval('descsequence'); --returns 4
SELECT nextval('descsequence'); --returns 3
SELECT nextval('descsequence'); --returns 2
SELECT nextval('descsequence'); --returns 1
SELECT nextval('descsequence'); --returns 5
SELECT nextval('descsequence'); --returns 4

Use Sequence with the Table

You can use a sequence to generate values for the INT column of a table.

For example, let's create the employee table as shown below.

CREATE TABLE employee (emp_id 	INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email	VARCHAR (100)
);

Create a new sequence and associate it with the employee table's emp_id column, as shown below.

CREATE SEQUENCE employee_seq
START 10
INCREMENT 5
MINVALUE 10
OWNED BY employee.emp_id;

Now, use the above employee_seq with the INSERT statement to insert values of the emp_id column, as shown below.

INSERT INTO employee(EMP_ID, FIRST_NAME, LAST_NAME, EMAIL)
VALUES
	(nextval('employee_seq'),'Annie','Smith','[email protected]'),
	(nextval('employee_seq'), 'Susan', 'Klassen', '[email protected]'),
	(nextval('employee_seq'), 'May', 'Kaasman', '[email protected]');

In the above example, the nextval() function fetches the value from employee_seq which will be inserted in the emp_id column. You can see emp_id is populated with values as 10, 15, and 20.

Drop the Sequence

You can remove the sequence using the DROP SEQUENCE statement.

If the sequence is associated with any table column, the sequence will be automatically dropped when the table column is dropped or the table is dropped.

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

The following statement drops the mysequence.

DROP SEQUENCE mysequence;

The following drops multiple sequences.

DROP SEQUENCE mysequence, employee_seq;

You can use the sequence as SERIAL data type. Learn about it next.