Serial and Identity

Learning Focus

Use this lesson to choose IDENTITY columns in PostgreSQL and understand how sequences relate to serial/identity.

Concept Overview

PostgreSQL provides two common auto-increment patterns:

• modern: GENERATED AS IDENTITY (recommended)
• legacy: serial / bigserial (works, older pattern)

Both are backed by sequences.

---

Identity Column (Recommended)

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

Variants:

• GENERATED ALWAYS: prevents manual inserts unless overridden
• GENERATED BY DEFAULT: allows manual inserts

---

Legacy serial

CREATE TABLE legacy (
  id bigserial PRIMARY KEY
);

---

Common Pitfalls

Pitfall	What happens	Fix
Manual inserts without sequence sync	Duplicate key errors later	Use identity or setval carefully
Missing sequence privileges for writer roles	Inserts fail	Grant on sequences

What's Next

• Next: boolean
• Section Overview