Serial and identity columns in PostgreSQL

Introduction

Serial and identity columns help automatically create unique numbers for new rows. This makes it easy to give each row a special ID without typing it yourself.

When you want each row in a table to have a unique number automatically.

When adding new records and you don't want to manually assign IDs.

When creating a list of items where each needs a unique identifier.

When you want to keep track of rows with a simple number that increases.

When you want to avoid mistakes from typing duplicate IDs.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name SERIAL PRIMARY KEY
);

-- or using identity columns (PostgreSQL 10+)
CREATE TABLE table_name (
  column_name INT GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY PRIMARY KEY
);

SERIAL is a shortcut that creates an integer column and a sequence to auto-increment values.

IDENTITY columns are the SQL standard way and give more control over how numbers are generated.

Examples

This creates a table with an auto-incrementing id column using SERIAL.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

This creates a table with an identity column that always generates a new number for order_id.

PostgreSQL

CREATE TABLE orders (
  order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name TEXT
);

This creates a table where product_id is auto-generated but you can also insert your own number if needed.

PostgreSQL

CREATE TABLE products (
  product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name TEXT
);

Sample Program

This example creates an employees table with a SERIAL column for employee_id. It inserts two employees and shows their IDs automatically assigned.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  employee_name TEXT
);

INSERT INTO employees (employee_name) VALUES ('Alice');
INSERT INTO employees (employee_name) VALUES ('Bob');

SELECT * FROM employees ORDER BY employee_id;

Important Notes

SERIAL is easy to use but is PostgreSQL-specific.

IDENTITY columns follow the SQL standard and are recommended for new projects.

Always use PRIMARY KEY with these columns to ensure uniqueness.

Summary

Serial and identity columns automatically create unique numbers for new rows.

SERIAL is a quick PostgreSQL shortcut; IDENTITY is the standard and more flexible.

Use them to avoid manual ID entry and prevent duplicates.