How to Define Auto Increment in PostgreSQL

In this PostgreSQL tutorial, I will show how to define auto increment in PostgreSQL. Also, you will understand the data types (like SERIAL and etc) which are used to define auto increment for the column.

Additionally, I will explain the internal working of how value is incremented automatically for the column which is defined as auto-increment.

How to Define Auto Increment in PostgreSQL

When you don’t use the primary key with every table in PostgreSQL, then it is not a good way to define the table columns. Every table should contain a primary key column. PostgreSQL provides several ways to define an auto-increment primary key for a specific column in the table.

PostgreSQL has data types SERIAL and BIGSERIAL that are used for the auto-increment purpose, which means you can define the column data type as SERIAL and BIGSERIAL, and the primary key for that column is added automatically.

You know the purpose of the primary key is to determine each row in the table uniquely. So always define the auto-increment primary key column in every table in PostgreSQL.

Let’s take examples and learn how to define the column as auto-increment while creating the table.

How to Define Auto Increment in PostgreSQL using SERIAL

The data type SERIAL of PostgreSQL generates the unique identifier (integer value) for the particular column in the table, especially used for the column that is defined as the primary key.

When you insert the data into the table, it automatically generates the unique integer value for the column.

Let’s take an example where you will create the table ’employees’ with columns ‘id’, ‘name’, and ‘country’.

Use the below statement to create the table ’employees’.

CREATE TABLE employees (
  id              SERIAL PRIMARY KEY,
  name           VARCHAR(50) NOT NULL,
  country  VARCHAR(50) NULL
);

Look at the above output, the column ‘id’ is defined as SERIAL data type and the primary key column of the table (id defined as the auto-increment column). This means the value of the column ‘id’ will be incremented automatically after performing any insertion on the table.

Now insert some data into the table ’employees’ and see how value is incremented for the column which is defined as auto-increment.

INSERT INTO employees(name,country)values
('James', 'USA'),
('Benjamin', 'United Kingdom'),
('Carl', 'United State of America');

After inserting the three records, see how the column ‘id’ value incremented from 1 to 3 in a sequenced manner. This is how you can define the auto-increment in Postgresql using serial data type.

Let’s understand the internal working of the auto-increment using the SERIAL data type.

• There is a term called sequence which can be anything in sequence but here you will understand the sequence which is related to the numbers or integers. That is to say, the sequence generates a series of numbers or integers in a specific order (like descending and ascending order).
• When you execute the statement to create the table ’employees’ which contains a column ‘id’ as SERIAL type. Behind the scenes, PostgreSQL creates a sequence object for that column, the next value is set for the column as the default value which is generated by the sequence.

There is a default format for sequence name which is tablename_columname_seq, for the ’employees’ table the sequence object name is employees_id_seq.

Now you know how to define an auto-increment column using SERIAL data type and sequence object and how this sequence is created.

But sometimes you want to define your own default value for the auto-increment column, actually when you inserted the data into the table ’employees‘, the default value for the column ‘id’ starts from 1.

Suppose you want to change the default value of the sequence and set your value like E100, then follow the below steps:

First, you need to create a sequence so here the sequence name will be employees_id_seq.

CREATE SEQUENCE employees_id_seq;

Now create a table named employees with columns ‘id’, ‘name’, and ‘country’

CREATE TABLE employees (
  id TEXT DEFAULT ('E' || nextval('employees_id_seq')),
  name VARCHAR(50) NOT NULL,
  country VARCHAR(50) NULL,
  PRIMARY KEY (id)
);

The column ‘id’ has data type ‘TEXT’ to store the string value, then the default value is set for this column using (‘E’ || nextval(’employees_id_seq’)). The ‘id’ column will store the custom format string value as an id for the employees.

• The ‘E’ is a prefix and is concatenated with ||, This nextval(’employees_id_seq’)) will get the value from the sequence ’employees_id_seq’ and its default value is 1.
• So whenever data is inserted into the table, the id column will have values like (E1, E2, … EN).

The default value of employees_id_seq is 1, so change the value to 100 using the setval() function of PostgreSQL.

SELECT setval('employees_id_seq', 100);

Let’s view the sequence default value using the below query.

SELECT * FROM employees_id_seq;

Let’s insert some data and see how the value of the id column is incremented based defined value for the sequence.

INSERT INTO employees (name, country) VALUES 
('Grace', 'USA'),
('Jack', 'Canada'),
('Samuel', 'Australia'),
('Margaret', 'USA');


SELECT * FROM employees;

If you look at the output, the value of the ‘id’ column is starting from E101, E102, and so on. This is how you can set the custom value for the sequence to get the desired type of ‘id’ column value.

Conclusion

In this PostgreSQL tutorial, you have learned how to define the column as an auto-increment in Postgresql to increment the column value automatically whenever you insert the record into the table. Then you learned about the data type SERIAL which uses for the auto-increment purpose, also you learned the internal workings of the auto-increment using the SERIAL data type.

You may also read:

• Postgresql row_number + Examples
• PostgreSQL add primary key
• PostgreSQL Data Types + Examples