PostgreSQL Bigserial datatype Guide

PostgreSQL is the most advanced open source database. PostgreSQL supports many data types and one of the special datatype is bigserial data type. Bigserial data type is used for creating autoincrement columns. PostgreSQL supports 3 datatypes for creating autoincrement columns.

1. smallserial
2. serial
3. bigserial

All three datatypes are used for autoincrement fields. All three datatypes have different storage requirements and supports different range of numbers.

Bigserial data type example

Below is the example of PostgreSQL bigserial data type.

CREATE TABLE employees
(
	id bigserial,
	name varchar(20)
);

When we insert three rows through below SQL, it will automatically insert increment values into column id.

INSERT INTO employee(name) VALUES ('k1');
INSERT INTO employee(name) VALUES ('k2');
INSERT INTO employee(name) VALUES ('k3');

In the above image, id column is auto populate with increment numbers.

We can also specify the id value, if we want to manually insert specific value into the column.

In the above image, we have specify id column value 4 into the insert query and it isadded into the employee table.

Problem: When we try to insert the duplicate value into the big serial data type, it will not give any error.

To avoid duplicate values, we can add PRIMARY KEY while creating the table in PostgreSQL.

PostgreSQL bigserial primary key

In the below SQL, I have create a new table with bigserial datatype as primary key.

CREATE TABLE employee
(
	ID bigserial PRIMARY KEY,
	name varchar(20)
);

In below image, I have added a duplicate entry into the bigserial primary key column and we got the duplicate key violates exception.

Conclusion: PostgreSQL supports three data types smallserial, serial, and bigserial for autoincrement columns. To make unique entries into the autoincrement column, we must use PRIMARY KEY constraint with bigserial data type.