Sequence
A sequence is a database object that generates a series of unique numeric values, most commonly used to auto-generate primary key IDs.
Overview
A sequence is a standalone schema object that produces an ordered, typically incrementing, series of numbers independent of any table. Each call to advance it returns a new, unique value — making sequences the standard mechanism for auto-incrementing surrogate keys in SQL databases that don't use an identity-column shorthand.
Copy code
CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1;
SELECT nextval('order_id_seq'); -- 1
SELECT nextval('order_id_seq'); -- 2
START WITH sets the initial value, INCREMENT BY sets the step (can be negative for a descending sequence), and MINVALUE/MAXVALUE/CYCLE control bounds and wraparound behavior.
Using a sequence for auto-incrementing IDs
The most common pattern attaches a sequence to a column's DEFAULT:
Copy code
CREATE SEQUENCE customer_id_seq;
CREATE TABLE customers (
id INTEGER PRIMARY KEY DEFAULT nextval('customer_id_seq'),
name VARCHAR
);
INSERT INTO customers (name) VALUES ('Ada Lovelace'); -- id auto-fills to 1
DuckDB specifics
DuckDB implements CREATE SEQUENCE with the standard options (START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE/NO CYCLE), and the nextval('seq_name') / currval('seq_name') functions to advance and read a sequence. DuckDB does not have a dedicated IDENTITY/AUTO_INCREMENT column keyword the way some other databases do — a DEFAULT nextval('seq_name') on the column is the idiomatic DuckDB equivalent, and can even be added to an existing table with ALTER TABLE tbl ADD COLUMN id INTEGER DEFAULT nextval('seq_name').
Related terms
A DEFAULT value is a value a database column automatically takes on when an INSERT statement doesn't explicitly specify a value for it.
GENERATE_SERIES →GENERATE_SERIES(start, stop, step) produces a sequence of numeric, date, or timestamp values, commonly used to build calendars or fill gaps in time-series data.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
Surrogate key →A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.
ORDER BY clause →The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.
FAQS
Create a sequence with CREATE SEQUENCE, then set the column's DEFAULT to nextval('sequence_name'). DuckDB doesn't have a dedicated IDENTITY/AUTO_INCREMENT keyword, so a sequence-backed default is the standard approach.
nextval('seq') advances the sequence and returns the new value. currval('seq') returns the value most recently produced by nextval() in the current session without advancing the sequence further, and errors if nextval() hasn't been called yet.
