How To Set Auto Increment In PostgreSQL

In this PostgreSQL tutorial, I will show you how to define auto-increment in PostgreSQL. Additionally, you will understand the data types (such as SERIAL) used to define auto-increment for the column.

How To Set Auto Increment In PostgreSQL

When you don’t use the primary key with every table in PostgreSQL, then it is not a good way to define the table columns. Every table should contain a primary key column. PostgreSQL offers several methods for determining an auto-increment primary key for a specific column in a table.

PostgreSQL has data types SERIAL and BIGSERIAL that are used for auto-increment purposes, which means you can define the column data type as SERIAL or BIGSERIAL, and the primary key for that column is added automatically.

You know that the purpose of the primary key is to identify each row in the table uniquely. So, always define the auto-increment primary key column in every table in PostgreSQL.

Let’s take examples and learn how to define the column as auto-increment while creating the table.

Approach 1: Using SERIAL

The data type SERIAL of PostgreSQL generates a unique identifier (integer value) for the particular column in the table, primarily used for the column that is defined as the primary key.

When you insert the data into the table, it automatically generates a unique integer value for the column.

Let’s take an example where you will create the table ’employees’ with columns ‘id’, ‘name’, and ‘country’.

Use the statement below to create the ’employees’ table.

CREATE TABLE employees (
  id              SERIAL PRIMARY KEY,
  name           VARCHAR(50) NOT NULL,
  country  VARCHAR(50) NULL
);

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the above output, the column ‘id’ is defined as a SERIAL data type and the primary key column of the table (id is defined as the auto-increment column). This means that the value of the ‘id’ column will be incremented automatically after any insertion into the table.

Now, insert some data into the ’employees’ table and observe how the value is incremented for the column defined as auto-increment.

INSERT INTO employees(name,country)values
('James', 'USA'),
('Benjamin', 'United Kingdom'),
('Carl', 'United State of America');

After executing the above query, I obtained the expected output, as shown in the screenshot below.

After inserting the three records, see how the column ‘id’ value increments from 1 to 3 in a sequenced manner. This is how you can define the auto-increment in PostgreSQL using the serial data type.

Let’s understand the internal working of the auto-increment using the SERIAL data type.

• There is a term called sequence, which can be anything in sequence, but here you will understand the sequence that is related to the numbers or integers. That is to say, the sequence generates a series of numbers or integers in a specific order (like descending and ascending order).
• When you execute the statement to create the table ’employees’, which contains a column ‘id’ of type SERIAL, PostgreSQL creates a sequence object behind the scenes for that column. The next value is set as the default value for the column, which is generated by the sequence.

There is a default format for sequence name, which is tablename_columname_seq. For the ’employees’ table, the sequence object name is employees_id_seq.

Now you know how to define an auto-increment column using the SERIAL data type and a sequence object, as well as how this sequence is created.

However, sometimes you want to define your own default value for the auto-increment column. When inserting data into the ’employees‘ table, the default value for the ‘id’ column starts from 1.

Suppose you want to change the default value of the sequence and set your value like E100, then follow the steps below:

First, you need to create a sequence; here, the sequence name will be employees_id_seq.

CREATE SEQUENCE employees_id_seq;

Now create a table named employees with columns ‘id’, ‘name’, and ‘country’

CREATE TABLE employees (
  id TEXT DEFAULT ('E' || nextval('employees_id_seq')),
  name VARCHAR(50) NOT NULL,
  country VARCHAR(50) NULL,
  PRIMARY KEY (id)
);

The column ‘id’ has data type ‘TEXT’ to store the string value, then the default value is set for this column using (‘E’ || nextval(’employees_id_seq’)). The ‘id’ column will store the custom format string value as an ID for the employees.

• The ‘E’ is a prefix and is concatenated with ||, This nextval(’employees_id_seq’)) will get the value from the sequence ’employees_id_seq’, and its default value is 1.
• Whenever data is inserted into the table, the ID column will have values such as (E1, E2, …, EN).

The default value of employees_id_seq is 1, so change the value to 100 using the setval() function of PostgreSQL.

SELECT setval('employees_id_seq', 100);

Let’s view the sequence default value using the query below.

SELECT * FROM employees_id_seq;

Let’s insert some data and see how the value of the id column is incremented based on a defined value for the sequence.

INSERT INTO employees (name, country) VALUES 
('Grace', 'USA'),
('Jack', 'Canada'),
('Samuel', 'Australia'),
('Margaret', 'USA');


SELECT * FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

If you examine the output, the values of the ‘id’ column start from E101, E102, and so on. This is how you can set the custom value for the sequence to get the desired type of ‘id’ column value.

Approach 2: Using BIGSERIAL for Large Tables

We can also use the BIGSERIAL while creating larger tables. Execute the below query for that purpose.

CREATE TABLE patient_events (
    event_id BIGSERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

After executing the above query, I got the expected output as shown in the screenshot below.

When to Choose BIGSERIAL Over SERIAL

• Your table is expected to grow beyond ~1 billion rows
• You’re designing for long-term scalability
• The additional 4 bytes of storage per row are acceptable
• You want to avoid future migration headaches

Approach 3: Using IDENTITY Columns (PostgreSQL 10+)

Starting with PostgreSQL 10, you can use the SQL standard IDENTITY columns, which offer a more standard approach compared to the PostgreSQL-specific SERIAL type.

We can execute the query below when creating a new table.

CREATE TABLE customer (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

After executing the above query, I got the expected output as shown in the screenshot below.

Approach 4: Using Sequences Explicitly

You can also create and manage sequences explicitly. This approach gives you more control over how the auto-incrementing values are generated.

We can execute the following query to create a sequence.

-- Create a sequence
CREATE SEQUENCE customersNew_id_seq
    START WITH 1000
    INCREMENT BY 5
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

After executing the above query, I got the expected output as shown in the screenshot below.

Now, let’s use the query below to create a table using the same sequence that I made above.

CREATE TABLE customerNew (
    id INTEGER PRIMARY KEY DEFAULT nextval('customersNew_id_seq'),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

In this PostgreSQL tutorial, you have learned how to make a column an auto-increment in PostgreSQL to increment the column value automatically whenever you insert a record into the table. Then you learned about the data type SERIAL, which is used for auto-increment purposes, and you learned about the internal workings of auto-increment using the SERIAL data type.

You may also read:

• Postgresql row_number + Examples
• PostgreSQL add primary key
• PostgreSQL Data Types + Examples