PostgreSQL – Create Table with Auto-increment ID

Create Table with Auto-increment ID in PostgreSQL Database

In PostgreSQL, creating a table with an auto-incrementing ID column is straightforward using the SERIAL or GENERATED AS IDENTITY data types. These data types automatically generate a unique value for the ID column whenever a new row is inserted.

In this tutorial, we will cover how to create tables with auto-increment IDs, including examples for starting at a specific value and incrementing by a custom amount.

---

Examples for Creating Table with Auto-increment ID

The following are examples where we create a table with Auto-increment ID using SERIAL, create a table with Auto-increment ID starting from a specific value, create a table with Auto-increment ID with custom increment.

---

Example 1: Basic Auto-increment ID Using SERIAL

To create a table with an auto-incrementing ID column using SERIAL, use the following syntax:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

Explanation:

• The SERIAL data type automatically creates a sequence to generate unique values for the employee_id column.
• Each new row inserted into the employees table will have an incrementing value in the employee_id column.

Insert sample data into the table:

INSERT INTO employees (employee_name, department)
VALUES 
('Alice', 'HR'),
('Bob', 'IT'),
('Charlie', 'Finance');

View the table data:

SELECT * FROM employees;

Each new row inserted into the employees table has an incrementing value in the employee_id column, starting from 1, and incrementing in steps of one.

---

Example 2: Auto-increment ID Starting from a Specific Value

You can customize the starting value of the auto-increment column by manipulating the underlying sequence:

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;

Explanation:

• The SERIAL data type automatically creates a sequence named departments_department_id_seq.
• The ALTER SEQUENCE command sets the starting value of the sequence to 100.

Insert sample data:

INSERT INTO departments (department_name)
VALUES 
('HR'),
('IT'),
('Finance');

View the table data to confirm the custom starting value:

SELECT * FROM departments;

The starting value of department_id is set to 100, and increments by one for every subsequent entry.

---

Example 3: Auto-increment ID with Custom Increment

You can customize the increment value of the auto-increment column by modifying the sequence:

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;

Explanation:

• The SERIAL data type creates a sequence named projects_project_id_seq.
• The ALTER SEQUENCE command sets the increment value of the sequence to 5, so the IDs will be 1, 6, 11, and so on.

Insert sample data:

INSERT INTO projects (project_name)
VALUES 
('Project A'),
('Project B'),
('Project C');

View the table data:

SELECT * FROM projects;

The ALTER SEQUENCE command has set the increment value of the sequence to 5, so the IDs are 1, 6, 11.

---

Example 4: Using GENERATED AS IDENTITY

PostgreSQL 10 introduced the GENERATED AS IDENTITY data type as a more standardized way to define auto-incrementing columns. Here’s an example:

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

Explanation:

• The GENERATED ALWAYS AS IDENTITY clause generates unique values for the task_id column.
• This method is more ANSI SQL compliant than using SERIAL.

Insert sample data:

INSERT INTO tasks (task_name)
VALUES 
('Task 1'),
('Task 2');

View the table data:

SELECT * FROM tasks;

---

Conclusion

PostgreSQL provides multiple ways to define an auto-incrementing ID column, including SERIAL and GENERATED AS IDENTITY. You can further customize the behavior of the ID column by modifying the sequence for starting values, increments, and more.