A 5-Minute Guide to Auto-Increment Primary Key in PostgreSQL

In PostgreSQL, each row in a table is uniquely identified by its primary key. The values in the column are verified to be unique and non-null by the primary key. In most of the scenarios, we create an auto-increment primary key in PostgreSQL. The auto-increment primary key automatically generates a new value for every new row inserted. In this post, I explain all the scenarios and examples related to auto-increment as the primary key.

Introduction

There are two ways to add auto-increment columns to a PostgreSQL table.

1. Serial Data Types
2. Identity Columns

Serial Data Types

PostgreSQL has three built-in data types specifically designed to handle auto-increment scenarios. Below are the data types:

Data Type	Equal to	Storage size	Max Value supported
smallserial	smallint	2 bytes	32,767
serial	integer	4 bytes	2,147,483,647
bigserial	bigint	8 bytes	9,223,372,036,854,775,807

Choose serial data types based on the size of the dataset. If your dataset estimate size is larger, then go with bigserial, or if you are working with a medium dataset size, then choose serial data type; for a very small dataset, choose smallserial data type.

These serial data types automatically create a sequence generator, which starts with 1 and increments with 1 and can go up to their maximum supported value. They will also add a non-null constraint to a column to ensure there will be no null value in the column. You need to add PRIMARY KEY after the serial data type to make it an auto-increment primary key in PostgreSQL.

To read more about the primary key, read the official documentation of PostgreSQL here.

Below are examples of all three data types while creating a primary key table.

--create table using smallserial data type
CREATE TABLE smallserial_names
(
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(20)
);


--create table using serial data type
CREATE TABLE serial_names
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(20)
);

--create table using bigserial data type
CREATE TABLE bigserial_names
(
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(20)
);

--create table using smallserial data type
CREATE TABLE smallserial_names
(
	id SMALLSERIAL PRIMARY KEY,
	name VARCHAR(20)
);


--create table using serial data type
CREATE TABLE serial_names
(
	id SERIAL PRIMARY KEY,
	name VARCHAR(20)
);

--create table using bigserial data type
CREATE TABLE bigserial_names
(
	id BIGSERIAL PRIMARY KEY,
	name VARCHAR(20)
);

Now, we can use the INSERT query to insert data into the table. We don’t need to specify the “id” column name in the query. PostgreSQL will automatically generate a new ID for this column. Below is an example.

INSERT INTO smallserial_names (name) VALUES ('name1');
INSERT INTO serial_names (name) VALUES ('name2');
INSERT INTO bigserial_names (name) VALUES ('name3');

As shown in the above screenshot, 1 is inserted into the first row. Next time, when we insert again into the table, PostgreSQL will insert 2 in the second row in the id column.

We can also specify the ID column value in the INSERT query, but if the value is not inserted before, PostgreSQL will not throw any errors and run the INSERT query successfully.

But if the value is already inserted, PostgreSQL will throw an error like shown below:

We can also use the auto-increment column in the INSERT query without providing the new value. In this case, PostgreSQL will automatically generate a new value for the column. To do this, use the DEFAULT keyword instead of the value shown below:

INSERT INTO serial_names (id, name) VALUES (DEFAULT, 'George Yarbrough');

IDENTITY Columns

Identity columns are used when you need more control over how auto-incremented columns will behave. With the Identify columns, you can specify the below values:

1. Starting Value
2. Min Value
3. Max Value
4. Increment By Value

Below is an example of creating an Auto-Increment Primary Key in PostgreSQL using “IDENTITY” columns.

CREATE TABLE work_schedule
(
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647),
  name varchar(20)
)

We can also modify parameters for identity. Suppose we need to start the auto-increment column from 1000, and that should increment by 10. To achieve this, change the start and min values to 1000 and the increment by 10, as shown below.

CREATE TABLE work_schedule
(
	id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10 MINVALUE 1000 MAXVALUE 2147483647),
	name varchar(20)
)

Now, when we insert two rows into the table,. The first ID will be generated as 1000, and the next row ID will be 1010.

Summary

PostgreSQL primary keys are used unique row identification. Choose between Serial data types (smallserial, serial, bigserial) for simple scenarios and Identity columns for more control on auto-increment column. With Identity column, you can set starting value, minimum value, increment value, and maximum value.

Read More:

• Effective way to use COALESCE in PostgreSQL
• 2 Easy ways to handle the ISNULL function in PostgreSQL