SQL Sequences

A sequence hands out numbers, one at a time. With the default settings it never repeats one. 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 with max(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

With the usual ascending, non-cycling setup, each call to a sequence advances to a new value. That does not make the values consecutive in the table, and treating a gap as a missing row can send you looking for data that was never there. Sequences can also be configured to decrement or cycle, so do not treat ordering or uniqueness as a universal property of every sequence definition.

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 gone: the next insert gets the one after it.

The collision that catches everyone

A sequence does not automatically synchronize itself to explicit values you insert into a table. If you load an ID by hand, the sequence keeps advancing from its own state and can eventually hand out a value that is already present.

-- 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 and 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 nextval fails 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 a Dependency Error, and for the same reason CREATE OR REPLACE SEQUENCE fails once a table uses it as a default, because replacing means dropping first. Detach it with ALTER 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 when you need generated values stored with rows. See window functions.