Sequences and Column Defaults

A sequence is a standalone database object that generates a series of unique numbers — it's the mechanism behind every auto-incrementing id you've been using with SERIAL.

What SERIAL Actually Does

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

Behind the scenes, this is shorthand for creating a sequence and wiring it up as the column's default:

CREATE SEQUENCE employees_id_seq;
CREATE TABLE employees (
  id INTEGER PRIMARY KEY DEFAULT nextval('employees_id_seq'),
  name TEXT
);
ALTER SEQUENCE employees_id_seq OWNED BY employees.id;

Creating and Using a Sequence Directly

CREATE SEQUENCE order_number_seq START 1000 INCREMENT 1;

SELECT nextval('order_number_seq');  -- 1000
SELECT nextval('order_number_seq');  -- 1001
SELECT currval('order_number_seq');  -- 1001 (the last value THIS session generated)

GENERATED ... AS IDENTITY (the Modern Alternative to SERIAL)

The SQL-standard way to get the same auto-increment behavior:

CREATE TABLE employees (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

GENERATED ALWAYS rejects any attempt to manually insert a value into id; GENERATED BY DEFAULT allows it (falling back to the sequence only when no value is given) — closer to how SERIAL behaves.

Resetting a Sequence

ALTER SEQUENCE employees_id_seq RESTART WITH 1;

Useful after TRUNCATE ... RESTART IDENTITY (see the Drop vs Truncate lesson), or when re-seeding test data.

Why IDs Can Have Gaps

Sequences don't roll back with a failed transaction — if a transaction calls nextval() and then rolls back, that number is gone forever, never reused. Gaps in your IDs are completely normal and not a bug.

Common Errors & Troubleshooting

• IDs "jumping" after a failed insert — this is expected: a sequence's nextval() call is not rolled back even if the surrounding transaction fails, so gaps in IDs are normal, not corruption.
• ERROR: duplicate key value violates unique constraint "employees_pkey" after manually inserting an ID — if you insert an explicit id value yourself, the sequence doesn't know about it and may later generate that same number again; use SELECT setval('employees_id_seq', (SELECT MAX(id) FROM employees)); to resync it.
• nextval() giving an unexpected starting value after a restore — sequences are separate objects from the table's data; restoring a table's rows doesn't automatically fix its sequence's current position — resync it with setval() as above.

Key Takeaways

• SERIAL is convenient shorthand for "create a sequence, then use it as this column's default."
• GENERATED ... AS IDENTITY is the modern, SQL-standard equivalent — prefer it in new schemas.
• Gaps in auto-incremented IDs are expected behavior (from rolled-back transactions), not something to fix.