How do auto-incrementing IDs work (SERIAL, sequences, IDENTITY)?

Question

Accepted Answer

PostgreSQL generates auto-incrementing IDs (typically for primary keys) using **sequences** — special objects that produce unique increasing numbers. **`SERIAL`** is shorthand that creates a sequence automatically, and **`IDENTITY`** is the modern SQL-standard equivalent.

## SERIAL — the classic shorthand

```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,      -- auto-incrementing integer (SERIAL = INT + a sequence)
  name TEXT
);
-- BIGSERIAL for larger ranges (BIGINT-backed) — use for tables expecting many rows

INSERT INTO users (name) VALUES ('Ann');   -- id auto-assigned (1, 2, 3, ...)
```

`SERIAL` automatically creates a sequence and sets the column's default to pull the next value from it. `BIGSERIAL` uses `BIGINT` for larger ranges (important for tables that may exceed ~2 billion rows).

## IDENTITY — the modern, recommended way (Postgres 10+)

```sql
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- SQL-standard, recommended
  name TEXT
);
-- GENERATED ALWAYS → you can't manually insert a value (cleaner)
-- GENERATED BY DEFAULT → allows manual override when needed
```

`GENERATED ... AS IDENTITY` is the modern, SQL-standard approach (preferred over `SERIAL` in new code) — it's cleaner and avoids some `SERIAL` quirks (like ownership/permission edge cases).

## How sequences work underneath

```sql
CREATE SEQUENCE my_seq START 1 INCREMENT 1;
SELECT nextval('my_seq');     -- get the next value (advances the sequence)
SELECT currval('my_seq');     -- the last value YOU got
SELECT setval('my_seq', 100); -- set the current value
```

A sequence is an independent counter. `SERIAL`/`IDENTITY` use one behind the scenes. Note: sequences are **not** rolled back by transactions (so IDs can have gaps after rollbacks — normal and expected).

## Getting the new ID

```sql
-- RETURNING — get the generated id back from an INSERT (very useful)
INSERT INTO users (name) VALUES ('Ann') RETURNING id;
```

## Why it matters

Auto-incrementing IDs are fundamental — nearly every table needs a unique primary key, and auto-generated sequential IDs are the standard approach, so understanding how PostgreSQL handles them is essential everyday knowledge.

Knowing **`SERIAL`** (the classic, common shorthand that creates a sequence automatically) and especially **`IDENTITY`** (`GENERATED ... AS IDENTITY` — the modern, SQL-standard, recommended approach for new code, which is cleaner and avoids SERIAL's quirks) is important for defining primary keys correctly.

Understanding the underlying **sequences** (independent counters that produce unique increasing values) demystifies how auto-increment works and is useful for advanced control (`nextval`, `setval`).

Two practical points matter: choosing **`BIGSERIAL`/BIGINT** for tables that may grow large (avoiding integer overflow — a real concern), and knowing that **sequences aren't rolled back** by transactions (so IDs can have gaps after rollbacks or failed inserts — normal, expected behavior that surprises some).

The **`RETURNING`** clause for getting the generated ID back from an INSERT is also valuable, everyday knowledge.

Since every table needs primary keys and auto-incrementing IDs are the standard, and since understanding SERIAL vs the recommended IDENTITY, the underlying sequences, sizing (BIGSERIAL), gap behavior, and RETURNING covers the practical needs, knowing how PostgreSQL generates auto-incrementing IDs is fundamental, frequently-applied knowledge for working with Postgres tables and a common point where knowing the modern best practice (IDENTITY) reflects current Postgres competence.