Postgresql auto increment + Examples

In this Postgresql tutorial, we will learn about “Postgresql auto increment” using different auto-increment data types of Postgresql that create a unique identifier for each record in a column.

• Postgresql auto increment primary key
• Postgresql auto increment column
• Postgresql auto increment max value
• Postgresql auto increment primary key alter table
• Postgresql auto increment start value
• Postgresql auto increment reset
• Postgresql auto increment sequence
• Postgresql auto increment uuid
• Postgresql auto increment serial primary key

Before beginning, we need to know, In Postgresql to create the auto-increment, we use data types that are smallserial, serial, and bigserial.

• smallserial: It creates a smallint column.
• serial: It creates an integer column
• bigserial: It creates a bigint column, use for more than 2 to the power 31 identifiers over the lifetime of the table.

Some of the terms that we need to know about.

1. Auto-increment: Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
2. Primary Key: It is a unique identifier for each record in any table.
3. Sequence: It is a number generator that generates the sequence of a number between the minimum and maximum value.

Postgresql auto increment primary key

In Postgresql first, we need to create a table with some data in it. Then define auto increment using serial data type for a column that column acts as the primary key.

CREATE TABLE emp (id SERIAL PRIMARY KEY,emp_name VARCHAR);

Now insert the following records in the emp table one by one.

INSERT INTO emp(emp_name)values('Ron');

INSERT INTO emp(emp_name)values('Tom');

INSERT INTO emp(emp_name)values('Alice');

As we can see in the above output, Id for each employee is generated automatically. we haven’t provided any data to the id column for an employee in the insert statement of the table emp.

Read: PostgreSQL CREATE INDEX

Postgresql auto increment max value

In Postgresql, sometimes when we want to set the max value for a column that increments its value automatically. To set the max value for that we are going to use the sequence.

Let’s create the sequence named seq.

CREATE SEQUENCE seq
   START 1
   INCREMENT 1
   MAXVALUE 4;

In the above command, we have created a sequence or sequence of numbers from 1 to 4. We will use the nextval( ) function to access the value of the sequence while inserting data into the table.

Now create the table emp and insert 5 records using sequence ( seq ) that we have created using the above command.

CREATE TABLE emp (id SERIAL PRIMARY KEY,emp_name VARCHAR);

INSERT INTO emp(id,emp_name)values(nextval('seq'),'Ron'); 1
INSERT INTO emp(id,emp_name)values(nextval('seq'),'Tom'); 2
INSERT INTO emp(id,emp_name)values(nextval('seq'),'Alice'); 3
INSERT INTO emp(id,emp_name)values(nextval('seq'),'Jack'); 4

INSERT INTO emp(id,emp_name)values(nextval('seq'),'Jhon'); 5

SELECT * FROM emp;

As we can see, we haven’t got an error while inserting four records one by one till the max value. But as soon as, we inserted the fifth record, we got errors like “nextval: reached maximum value of sequence “seq” (4)”.

To solve the error just increase the max value in the sequence according to your need.

Read: Postgresql import SQL file

Postgresql auto increment column

In Postgresql, When we want to make any column act as auto-increment or we want that column value should increment automatically.

We can use any of three data types smallserial, serial, and bigserial. All you need to do is to while defining table schema or creating a table, define its column as any of the three data types.

Use the below command for your better understanding.

CREATE TABLE student(
   id SERIAl,
   name TEXT,
);

In the above command, we have defined table structure with id as an auto-increment column of SERIAL and name column a text data type. Let’s insert the following records.

INSERT INTO student (name) VALUES
('Charles'),
('Santos A. Best'),
('Brandon M'),
('Andrew J. Bryson'),
('Daniel B. Stewart');

As we have inserted five records into the table named student, integer values for the id column are generated automatically.

Read: Postgresql generate_series

Postgresql auto increment start value

While inserting a record in the table of the Postgresql database, sometimes we want to set the different starting values for the record, for setting different start values use the below command.

CREATE SEQUENCE seq
   START 2
   INCREMENT 1;
   
CREATE TABLE emp (id INTEGER DEFAULT nextval('seq'),emp_name VARCHAR);

INSERT INTO emp(emp_name)values('Ron');
INSERT INTO emp(emp_name)values('Tom');
INSERT INTO emp(emp_name)values('Alice');
INSERT INTO emp(emp_name)values('Jack');
INSERT INTO emp(emp_name)values('Jhon');

SELECT * FROM emp;

As we can see in the above output record id started from 2, first, we have created a sequence seq with the start value of 2.

We are using the “nextval( ‘seq’ )” function in the id column of the table emp to generating a unique id number starting with 2 for each record.

Read: Postgresql cast int

Postgresql auto increment primary key alter table

In Postgresql, sometimes we want to make the existing auto-increment column as the primary key of our table use the below instructions.

Let’s create the table name emp_data.

CREATE TABLE emp_data (emp_id SMALLSERIAL,emp_name VARCHAR)

Now insert the following records.

INSERT INTO emp_data(emp_name)values('Brad P. Witman');
INSERT INTO emp_data(emp_name)values('Ron C. McNichols');
INSERT INTO emp_data(emp_name)values('Charles E. Smith');
INSERT INTO emp_data(emp_name)values('Victor T. Holloway');

Above created table with auto increment emp_id, which is not the primary key column. Let’s make that column the primary key use the below command.

ALTER TABLE emp_data
  ADD CONSTRAINT emp_id_pk
  PRIMARY KEY (emp_id)

We have created a primary key on the existing table emp_data called emp_id_pk. It consists of the emp_id column.

Read: How to find primary column name in Postgresql

Postgresql auto increment reset

In Postgresql, to reset the auto-increment row ID in a Postgres database, here is a command TRUNCATE that removes all data and reset auto increment to 0.

Use the below command.

TRUNCATE <TABLENAME> RESTART IDENTITY;

Where <TABLENAME> is the name of the table.

Let’s create the table quickly and insert some records into it.

CREATE TABLE emp_data(emp_id SMALLSERIAL,emp_name VARCHAR);

INSERT INTO emp_data(emp_name)values('Brad P. Witman');
INSERT INTO emp_data(emp_name)values('Ron C. McNichols');
INSERT INTO emp_data(emp_name)values('Charles E. Smith');
INSERT INTO emp_data(emp_name)values('Victor T. Holloway');

Now you have created table emp_data with some records and auto-created id for the emp_id column.

Let’s reset this emp_id to 0 using the TRUNCATE command.

TRUNCATE emp_data RESTART IDENTITY;

SELECT * FROM emp_data;

Read: Update query in PostgreSQL

Postgresql auto increment sequence

In Postgresql, we can use auto-increment with sequence to provide unique value to each record in the table.

• First, create the sequence using the below command.

CREATE SEQUENCE data_seq
     START 1
     MAXVALUE 20
     CACHE 1;

In the above command, we are creating a sequence name data_seq with starting value as 1 and maximum as 20 and generating one value at a time using CACHE.

• Create a table name emp_data and link the emp_id column with sequence data_seq using ( ALTER SEQUENCE <SEQUENCE_NAME> OWNED BY TABLE_NAME.COLUMN_NAME ).

CREATE TABLE emp_data(emp_id BIGSERIAL,emp_name VARCHAR);
ALTER SEQUENCE data_seq OWNED BY emp_data.emp_id;

Let’s insert the following records.

INSERT INTO emp_data(emp_name)values('Brad P. Witman');
INSERT INTO emp_data(emp_name)values('Ron C. McNichols');
INSERT INTO emp_data(emp_name)values('Charles E. Smith');
INSERT INTO emp_data(emp_name)values('Victor T. Holloway');

SELECT * FROM emp_data;

Now we have created the table with an auto-increment column using sequence.

Read: Postgresql date_trunc function

Postgresql auto increment uuid

In Postgresql, we can not make any column as the primary key with auto-increment of UUID data type, but we have an alternate approach, to generate the UUID’s within the database is to use gen_random_uuid( ) function of pgcrypto extension.

The pgcrypto module allows us to use the cryptographic functions for PostgreSQL.

So, “How are we going to use this function?”. For that create the pgcrypto extension in the database using the below command.

CREATE EXTENSION pgcrypto;

This is how we load the pre-compiled shared library which provides functionality into our Postgresql database.

Let’s create the primary key column with auto-increment of UUID data type.

CREATE TABLE emp_info(
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   name TEXT,
   email TEXT
);

The above command creates the emp_info table with id, name, email column, in which the id column will increment automatically.

The reason the gen_random_uuid( ) will generate the UUID as a new record is inserted into the table.

Insert the following records in the emp_info table.

INSERT INTO emp_info (name,email) VALUES
('Charles','CharlesKGreeson@jourrapide.com'),
('Santos A. Best','SantosABest@teleworm.us'),
('Brandon M','BrandonMCountryman@armyspy.com'),
('Andrew J. Bryson','AndrewJBryson@jourrapide.com'),
('Daniel B. Stewart','DanielBStewart@rhyta.com');

As we can see in the above output, we have generated the data of the id column using gen_random_uuid( ).

Read: PostgreSQL TO_NUMBER() function

Postgresql auto increment serial primary key

In Postgresql, to use the auto-increment primary key column with the serial data type, please go to the first sub-topic of this tutorial “Postgresql auto increment primary key”.

For your information, use the below command for the auto-increment serial primary key.

CREATE TABLE student(
   id SERIAl PRIMARY KEY,
   name TEXT,
);

As we can see in the above code, the id column is defined as a SERIAL data type.

You may also like to read the following tutorials.

• PostgreSQL TO_CHAR function
• How to import CSV file into PostgreSQL
• Postgresql row_number
• Postgresql current_timestamp
• Postgresql listen_addresses
• PostgreSQL Order By

So in this tutorial, we have learned about “Postgresql auto increment” using different data types and methods. we have covered the following topics.

• Postgresql auto increment primary key
• Postgresql auto increment column
• Postgresql auto increment max value
• Postgresql auto increment primary key alter table
• Postgresql auto increment start value
• Postgresql auto increment reset
• Postgresql auto increment sequence
• Postgresql auto increment uuid
• Postgresql auto increment serial primary key