SERIAL and BIGSERIAL

What are SERIAL and BIGSERIAL?

SERIAL and BIGSERIAL are shorthand notations for creating an auto-incrementing integer column backed by a sequence. SERIAL uses a 4-byte integer; BIGSERIAL uses an 8-byte integer.

Syntax

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50)
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  order_date DATE
);

Modern alternative (SQL standard):

CREATE TABLE students (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR(50)
);

When to Use

• Auto-incrementing primary keys for most tables
• SERIAL for tables with fewer than ~2 billion rows
• BIGSERIAL for very large tables or high-throughput systems
• IDENTITY for new projects (SQL standard)

Key Points

1. Auto Sequence — SERIAL creates a sequence named tablename_colname_seq automatically.
2. Not Truly Auto-Increment — It is syntactic sugar for: INTEGER NOT NULL DEFAULT nextval('seq').
3. Gaps — Sequence values can have gaps (e.g., after failed inserts or rollbacks).
4. NOT NULL — SERIAL columns are implicitly NOT NULL.
5. IDENTITY Preferred — PostgreSQL 10+ supports GENERATED ALWAYS AS IDENTITY, which is the SQL standard.