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.

Serial datatypes in PostgreSQL
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 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');

PostgreSQL Bigserial insert script

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.

Postgresql Bigserial manually autoincrement

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.

Bigserial duplicate values PostgreSQL

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.

	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.

PostgreSQL bigserial primary key duplicate error

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.