Schema Design and ConstraintsLesson 3.4

How sequences and IDENTITY columns work in PostgreSQL

CREATE SEQUENCE, nextval currval setval, SERIAL vs IDENTITY, sequence options increment start minval maxval, gap behavior, resetting sequences

Sequences and Identity

Sequences are database objects that generate unique integer values. IDENTITY columns are a cleaner wrapper around them.

IDENTITY (recommended)

CREATE TABLE products (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
-- GENERATED BY DEFAULT allows manual override:
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Manual sequence

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

SELECT nextval('order_number_seq');  -- 1000
SELECT currval('order_number_seq');  -- 1000 (same session)
SELECT setval('order_number_seq', 5000);  -- reset

Gaps are normal

Sequences do not roll back when a transaction fails. If you insert a row and roll back, the sequence number is consumed. Gaps in your IDs are expected and do not indicate a problem. Never rely on sequential IDs being contiguous.

Reset after bulk import

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

After loading data with explicit IDs (e.g. from a migration), always reset the sequence so the next auto-generated ID does not conflict.