Create Table with Primary Key Autoincrement in PostgreSQL Database

In PostgreSQL, creating a table with a primary key that automatically increments is commonly done using the SERIAL data type or the ANSI-compliant GENERATED AS IDENTITY. These methods generate unique, sequential values for the primary key column whenever a new row is inserted.

In this tutorial, we will learn how to create such tables with detailed examples.

Example 1: Basic Auto-increment Primary Key Using SERIAL

Let’s create a simple table named students where the primary key column, student_id, automatically increments using the SERIAL data type.

</>
Copy
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT
);
PostgreSQL - Create Table with Primary Key Autoincrement - Example: Basic Auto-increment Primary Key Using SERIAL

Insert some sample data into the table:

</>
Copy
INSERT INTO students (name, age)
VALUES 
('Alice', 20),
('Bob', 22),
('Charlie', 23);

Now, retrieve all rows from the students table:

</>
Copy
SELECT * FROM students;

Result:

Explanation: The student_id column automatically generates sequential values starting from 1. Each inserted row receives a unique ID, ensuring that every row can be uniquely identified.

Example 2: Customizing the Starting Value of Auto-increment

By default, the auto-increment starts at 1, but you can change the starting value of the sequence associated with the primary key. Let’s create a departments table and set the starting value of the primary key to 100.

</>
Copy
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Set the starting value of the sequence
ALTER SEQUENCE departments_department_id_seq RESTART WITH 100;
PostgreSQL - Create Table with Primary Key Autoincrement - Customizing the Starting Value of Auto-increment

Insert some sample data into the departments table:

</>
Copy
INSERT INTO departments (department_name)
VALUES 
('HR'),
('IT'),
('Finance');

Retrieve the data from the table:

</>
Copy
SELECT * FROM departments;

Result:

Explanation: The primary key column department_id starts from 100 instead of the default 1. The sequence ensures unique, incrementing values for each row.

Example 3: Auto-increment Primary Key with Custom Increment

You can customize the increment value of the primary key. For instance, if you want the IDs to increment by 5, you can modify the sequence accordingly. Let’s create a projects table:

</>
Copy
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL
);

-- Set the sequence to increment by 5
ALTER SEQUENCE projects_project_id_seq INCREMENT BY 5;
PostgreSQL - Create Table with Primary Key Autoincrement - Example: with Custom Increment

Insert sample data into the table:

</>
Copy
INSERT INTO projects (project_name)
VALUES 
('Project A'),
('Project B'),
('Project C');

Retrieve the data from the table:

</>
Copy
SELECT * FROM projects;

Result:

Explanation: The project_id column increments by 5 for each new row, producing values like 1, 6, 11, and so on. This is useful for scenarios where IDs need to follow a specific pattern or spacing.

Example 4: Using GENERATED AS IDENTITY

PostgreSQL 10 introduced the GENERATED AS IDENTITY data type as a more ANSI-compliant way to define auto-incrementing columns. Let’s create a tasks table with this method:

</>
Copy
CREATE TABLE tasks (
    task_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL
);

Insert sample data into the table:

</>
Copy
INSERT INTO tasks (task_name)
VALUES 
('Task 1'),
('Task 2');

Retrieve the data from the table:

</>
Copy
SELECT * FROM tasks;

Result:

Explanation: The GENERATED ALWAYS AS IDENTITY clause automatically generates unique values for the task_id column. Unlike SERIAL, this method does not rely on implicit sequences, making it more consistent with SQL standards.

Conclusion

PostgreSQL provides flexible ways to create tables with auto-incrementing primary keys using SERIAL or GENERATED AS IDENTITY. You can customize the starting values, increments, and sequences to suit your application’s requirements.