In PostgreSQL, sequences are a popularly used concept that allows users to generate a series of unique integers. Sequences can be created using a CREATE SEQUENCE command. The stated command can accept a variety of options such as START, INCREMENT, MINVALUE, CYCLE, etc. Using these options a user can create a customized sequence. Moreover, a sequence can be linked with a table’s column to create an auto-incremented column.

This post will explain how to use a SEQUENCE to create an auto-incremented column in Postgres.

How to Create an Auto-increment Column Using SEQUENCES in Postgres?

In PostgreSQL, usually, a sudo data type named SERIAL is used to create an auto-incremented column. However, the same functionality can be achieved in a more effective manner by associating a sequence with a specific table’s column. Use the following syntax to create an auto-incremented column via a Postgres sequence:

CREATE SEQUENCE seq_name
OWNED BY tab_name.column;

Consider the following example to understand this concept in a better way.

Example: Creating an Auto-increment Column Using a SEQUENCE

In the following code snippet, a table named “employee_tab” is created with the following columns: “id”, “emp_name”, and “emp_age”.

CREATE TABLE employee_tab(
id SMALLINT, 
emp_name TEXT,
emp_age SMALLINT
);

Now create a sequence named “employee_seq” and associate it with the “employee_tab” table:

CREATE SEQUENCE employee_seq
START 11
INCREMENT 2
MINVALUE 10
OWNED BY employee_tab.id;

In the above query, START, INCREMENT, and MINVALUE options are used to specify the starting value, increment value, and minimum value of the sequence. Lastly, the OWNED BY clause is used to associate the “employee_seq” sequence with the “id” column of the “employee_tab” table:

The sequence has been successfully created and associated with the “employee_tab” table. Now, execute the following query to insert the data into the “employee_tab” table:

INSERT INTO employee_tab(id, emp_name, emp_age)
VALUES (nextval('employee_seq'), 'Dean', 35),
(nextval('employee_seq'), 'Joseph', 29),
(nextval('employee_seq'), 'Tim', 25);

Here we utilized the “nextval()” function along with the INSERT statement to get the value from the “employee_seq” sequence and insert it into the “employee_tab” table:

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

SELECT * FROM employee_tab;

That was all about creating an auto-incremented column using a Postgres SEQUENCE.

Conclusion

In PostgreSQL, a sequence can be linked with a table’s column to create an auto-incremented column. For this purpose, use the “CREATE SEQUENCE” command along with the “OWNED BY” clause. Using this command users can create a customized sequence. This post has explained a detailed procedure for creating an auto-incremented column using a Postgres sequence.