SQL Sequences

A sequence hands out numbers, one at a time, never repeating. Ask it for a value with nextval and it gives you one and moves on. Attach it to a column as a default and rows get IDs without anyone choosing them.

create sequence customer_id_seq start 1;

select nextval('customer_id_seq') as first_id,
       nextval('customer_id_seq') as second_id;

The reason they exist

create table imported_customers (
  customer_id integer default nextval('customer_id_seq'),
  name varchar
);

insert into imported_customers (name)
values ('Ada'), ('Ben');

select * from imported_customers
order by customer_id;

The insert never mentions customer_id, so the default fires and the sequence supplies a value per row. Two concurrent writers get different numbers, which is the part you cannot reliably build yourself withmax(id) + 1.

Shaping the counter

create or replace sequence order_no_seq
  start 1000
  increment 10
  minvalue 1000
  maxvalue 9999
  cycle

CYCLE restarts at the minimum after the maximum is reached. Without it, running past the maximum is an error —nextval: reached maximum value of sequence — which is the safer default, because a wrapped ID collides with a row that already exists.

Gaps are normal

A sequence guarantees the numbers are distinct and increasing. It does not guarantee they are consecutive, and treating a gap as a problem will send you looking for missing rows that were never there.

Values are consumed as soon as nextval runs, and rolling back the transaction does not give them back. Take a value inside a transaction, roll the transaction back, and that number is simply gone — the next insert gets the one after it.

The collision that catches everyone

A sequence does not watch the table. Insert a row with an explicit ID and the counter has no idea — it keeps going from where it was, and eventually hands out a number that is already in the table.

-- the sequence supplies the next value, as designed
insert into imported_customers (name) values ('Cy');

-- someone loads a row with an ID of their own
insert into imported_customers values (999, 'Manual');

-- the counter never saw 999, so it carries on from where it was
insert into imported_customers (name) values ('Dee');

select * from imported_customers
order by customer_id;

Nothing fails here, because nothing collided yet. Dee lands on a small number while 999 sits far above it. It fails much later, once the counter climbs to 999 — usually in a bulk load, usually with a primary key error nobody can reproduce. Either let the sequence own the column, or reset it after a load that supplied its own IDs.

currval, and a caveat

currval returns the value this connection most recently drew. It is per-connection state, so calling it before any nextvalfails with currval: sequence is not yet defined in this session.

Inspecting and removing them

select sequence_name,
       start_value,
       increment_by,
       cycle,
       last_value
from duckdb_sequences()
order by sequence_name

last_value is null until the sequence has been used at least once.

Dropping a sequence a table depends on fails with aDependency Error, and for the same reasonCREATE OR REPLACE SEQUENCE fails once a table uses it as a default — replacing means dropping first. Detach it withALTER TABLE ... ALTER COLUMN ... DROP DEFAULT before you replace the sequence.

Row numbers are a different thing

row_number() also produces 1, 2, 3, and is not a substitute. It numbers the rows of one result, from one, in whatever order that query chose — run it again with a different filter and the same row gets a different number. A sequence value is written into the row and stays with it forever. Use row_number() for presentation and ranking; use a sequence for identity. Seewindow functions.