Understand sequences

A PostgreSQL sequence allocates values independently from a transaction. A rolled-back insert can leave a gap, and that is normal.

Identity columns commonly use a sequence behind the scenes:

CREATE TABLE notes (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title text NOT NULL
);

When PostgreSQL calls nextval, that value is consumed even if the transaction later rolls back or the insert conflicts. This prevents concurrent transactions from receiving the same value without making them wait for each other. It also means generated identifiers promise uniqueness, not a gapless business sequence.

Do not infer row count, ordering, or missing data from gaps. Use created_at for chronology and count(*) for a count. If invoice numbers must obey legal sequencing rules, model that requirement separately and understand the concurrency cost.

An import that supplies explicit identifiers can move the table ahead without moving its sequence. The next generated insert may then collide. Find the owned sequence and compare it with the data before changing anything:

SELECT pg_get_serial_sequence('notes', 'id');
SELECT max(id) FROM notes;

After confirming no concurrent import or insert is running, align the sequence deliberately:

SELECT setval(
  pg_get_serial_sequence('notes', 'id'),
  COALESCE((SELECT max(id) FROM notes), 1),
  EXISTS (SELECT 1 FROM notes)
);

The third argument matters. true means the next nextval advances beyond the supplied value; false means it returns that value first. The empty-table case above avoids skipping the initial value.

Never reset a live sequence merely to make IDs look consecutive. A lower value can collide with existing rows, and renumbering primary keys can break references, URLs, logs, and external systems.

Try it: insert a row inside a transaction, roll it back, then insert another row. Observe the gap and explain why it is safer than reclaiming the first value.