PostgreSQL Bigserial Datatype Guide

PostgreSQL is the most advanced open-source database. PostgreSQL supports many data types, and one of the special data types is bigserial data. The bigserial data type is used for creating auto-increment columns. PostgreSQL supports three data types for creating auto-increment columns.

1. smallserial
2. serial
3. bigserial

All three datatypes are used for auto-increment fields. All three datatypes have different storage requirements and support different ranges of numbers.

Datatype Name	Storage size	Range
smallserial	2 bytes	1 to 32767
serial	4 bytes	1 to 2147483647
bigserial	8 byes	1 to 9223372036854775807

Bigserial Data Type Example

Below is an example of a PostgreSQL big serial 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, the ID column is auto-populated with increment numbers.

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

In the above image, we have specified id column value 4 in the insert query, and it is added to 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 created a new table with the bigserial datatype as the primary key.

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

In the 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 the PRIMARY KEY constraint with the bigserial data type.