A Postgres sequence is an object of a database that generates a unique series of integers. In Postgres, sequences can be created using a “CREATE SEQUENCE” command. Sequences maintain a particular order, such as ascending or descending. The sequence’s order (ascending or descending) is determined at the time of sequence creation which plays a very crucial role. For instance, {2, 4, 6, 8, 10} and {10, 8, 6, 4, 2} are totally different sequences.

This write-up will present a comprehensive guide on sequence creation in Postgres using suitable examples.

How to Create a Sequence in Postgres?

In PostgreSQL, the CREATE SEQUENCE statement helps us create a new sequence. An additional clause IF NOT EXISTS can be utilized with the stated command to create a sequence only if it doesn’t exist already. The following syntax will let you understand how to create a sequence in Postgres:

CREATE SEQUENCE [ IF NOT EXISTS ] seq_name
[ AS integer_data_type ]
[ INCREMENT [ BY ] increment_value ]
[ MINVALUE min_value | NO MINVALUE ] 
[ MAXVALUE max_value | NO MAXVALUE ]
[ START [ WITH ] start_value ] 
[ CACHE cache ] 
[ [ NO ] CYCLE ]
[ OWNED BY { tab_name.col_name | NONE } ]

Let’s understand this syntax line-by-line:

- “seq_name” represents a new sequence to be created.
- Replace the “integer_data_type” option with one of the following data types: INTEGER, SMALLINT, or BIGINT.
- If you omit the data type while sequence creation, then Postgres will create the sequence with the default data type, i.e., BIGINT.
- The increment_value defines the sequence’s increment criteria.
- Specify any positive or negative value in place of “increment_value”. Where positive “increment_value” increments the sequence in ascending order, while negative “increment_value” will create a descending sequence.
- By default, the minimum value for an ascending sequence is 1, however, you can specify the minimum/lowest value of the sequence using the MINVALUE. Use the NO MINVALUE clause to specify the default minimum value of a sequence.
- Use the MAXVALUE to specify the maximum value of the sequence. A Postgres ascending sequence has a maximum/highest value equal to the maximum value of the specified data type. Use the NO MAXVALUE clause to specify the default maximum value of a sequence.
- The default minimum value for descending sequences is the lowest/minimum value of the given data type, while the default maximum value is “-1”.
- Use the START clause to define the initial value of a sequence, by default, it is equal to MINVALUE for ascending sequences and MAXVALUE for descending sequences.
- Using the CACHE, you can preallocate and store sequence numbers in memory for faster access.
- Enabling the CYCLE will restart the value of the sequence when it reaches the limit.
- The OWNED BY clause helps us in connecting the table’s column with the sequence. Therefore, dropping a table or column will also delete its associated sequence.

Example 1: How Does CREATE SEQUENCE Work in Postgres?

Let’s learn how to create a sequence in Postgres using the following code example:

CREATE SEQUENCE example_sequence
AS SMALLINT
INCREMENT 2
MAXVALUE 20
START 10;

In this example:

- A new sequence named “example_sequence” is created using the CREATE SEQUENCE command.
- The SMALLINT represents the type of sequence.
- The INCREMENT 2 represents that the sequence value will be incremented with 2.
- A positive increment value results in an ascending sequence.
- The sequence is started with a value 10.

The sequence has been successfully created. Let’s utilize the nextval() function to fetch the next value from the given sequence:

SELECT nextval('example_sequence');

Nextval() was executed three times, and this is what we got:

The output shows that the sequence value was incremented by 2 on each execution. Since we specified “20” as the maximum value. So, Postgres will throw an error if the nextval() function is used on a sequence that has reached its maximum limit:

The output shows an error stating that the “example_sequence” has reached its maximum limit. To rectify the stated error, you must use the “CYCLE” clause at the time of sequence creation.

Example 2: How to Create a Sequence in Descending Order?

In the following example, a negative INCREMENT value will be used to create a sequence in descending order:

CREATE SEQUENCE example_sequence_1
AS SMALLINT
INCREMENT -2
MINVALUE 1 
MAXVALUE 20
START 10
CYCLE;

Let’s run the nextval() function to fetch the next value from the given sequence:

SELECT nextval('example_sequence_1');

Following are the results of executing nextval() function three times:

The output shows that the sequence value decreases by 2 after each execution.

Example 3: How to Create a Sequence Associated/Linked With a Postgres Table?

A sequence can be created and used with an integer column of a table. In the following snippet, a new table named “student_tab” is created with three columns: “id”, “std_name”, and “std_age”.

CREATE TABLE student_tab(
id INTEGER, 
std_name TEXT,
std_age INTEGER
);

Now we will create a sequence named “std_seq” and associate it with the “student_tab” table:

CREATE SEQUENCE std_seq
START 1
INCREMENT 3
MAXVALUE 50
OWNED BY student_tab.id;

In the above query, START, INCREMENT, and MAXVALUE are used to specify the starting value as 1, increment value as 3, and sequence’s maximum value as 50. Finally, the OWNED BY clause is used to associate the “std_seq” sequence with the “id” column of the “student_tab” table:

The sequence has been successfully created and associated with the “student_tab” table. Now, we will execute the following piece of code to insert the data into the “student_tab” table:

INSERT INTO student_tab(id, std_name, std_age)
VALUES (nextval('std_seq'), 'Dean Jones', 35),
(nextval('std_seq'), 'Tim Joseph', 25);

In the above command, the “nextval()” function is used within the INSERT statement to get the value from the “std_seq” sequence and insert it into the “student_tab” table:

The output verifies that the selected records have been added to the “student_tab” table. Now, we will utilize the SELECT query to populate all the data of the “student_tab” table:

SELECT * FROM student_tab;

From the output, you can observe that the id column is populated based on the “std_seq” sequence.

Conclusion

To create a sequence in Postgres, execute the CREATE SEQUENCE command followed by the name of the sequence to be created. An additional clause IF NOT EXISTS can be utilized with the stated command to create a sequence only if it doesn’t exist already. Various options like START, INCREMENT, MAXVALUE, etc. can be used with the CREATE SEQUENCE command to specify sequence starting value, increment value, maximum value, and so on. This blog post demonstrated different use cases of the CREATE SEQUENCE command in PostgreSQL.