CREATE SEQUENCE

What is a Sequence?

A SEQUENCE is a database object that generates unique numeric values, typically used for auto-incrementing primary keys. SERIAL columns use sequences internally.

Syntax

CREATE SEQUENCE seq_name
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  NO MAXVALUE
  CACHE 1;

Using a sequence:

SELECT nextval('seq_name');   -- Get next value
SELECT currval('seq_name');   -- Current value (in this session)
SELECT setval('seq_name', 100); -- Set to specific value

When to Use

• Custom auto-increment logic beyond what SERIAL provides
• Sharing one sequence across multiple tables
• Generating invoice numbers, order numbers, etc.
• When you need specific start values or increments

Key Points

1. nextval — Advances the sequence and returns the new value. Thread-safe.
2. currval — Returns the last value generated in the current session.
3. setval — Manually sets the sequence position.
4. Gaps — Sequences can have gaps (failed transactions do not return values).
5. OWNED BY — Link a sequence to a column: ALTER SEQUENCE seq OWNED BY table.col.
6. SERIAL — SERIAL is shorthand for creating a sequence + default.