Skip to main content

CREATE SEQUENCE

Introduced or updated: v1.2.807

Creates a new sequence in Databend.

A sequence is an object that automatically generates unique numeric identifiers, commonly used for assigning distinct values to table rows (e.g., user IDs). While sequences guarantee unique values, they do not ensure contiguity (i.e., gaps may occur).

Syntax

CREATE [ OR REPLACE ] SEQUENCE [ IF NOT EXISTS ] <sequence>
[ START [ = ] <start_value> ]
[ INCREMENT [ = ] <increment_value> ]
ParameterDescriptionDefault
<sequence>The name of the sequence to be created.-
STARTThe initial value of the sequence.1
INCREMENTThe increment value for each call to NEXTVAL.1

Access control requirements

PrivilegeObject TypeDescription
CREATE SEQUENCEGlobalCreates a sequence.

To create a sequence, the user performing the operation or the current_role must have the CREATE SEQUENCE privilege.

note

The enable_experimental_sequence_rbac_check settings governs sequence-level access control. It is disabled by default. sequence creation solely requires the user to possess superuser privileges, bypassing detailed RBAC checks. When enabled, granular permission verification is enforced during sequence establishment.

This is an experimental feature and may be enabled by default in the future.

Examples

Basic Sequence

Create a sequence with default settings (starts at 1, increments by 1):

CREATE SEQUENCE staff_id_seq;

CREATE TABLE staff (
staff_id INT,
name VARCHAR(50),
department VARCHAR(50)
);

INSERT INTO staff (staff_id, name, department)
VALUES (NEXTVAL(staff_id_seq), 'John Doe', 'HR');

INSERT INTO staff (staff_id, name, department)
VALUES (NEXTVAL(staff_id_seq), 'Jane Smith', 'Finance');

SELECT * FROM staff;

┌───────────────────────────────────────────────────────┐
│ staff_id │ name │ department │
├─────────────────┼──────────────────┼──────────────────┤
2 │ Jane Smith │ Finance │
1 │ John Doe │ HR │
└───────────────────────────────────────────────────────┘

Custom Start and Increment

Create a sequence starting at 1000 with increment of 10:

CREATE SEQUENCE order_id_seq START = 1000 INCREMENT = 10;

CREATE TABLE orders (
order_id BIGINT,
order_name VARCHAR(100)
);

INSERT INTO orders (order_id, order_name)
VALUES (NEXTVAL(order_id_seq), 'Order A');

INSERT INTO orders (order_id, order_name)
VALUES (NEXTVAL(order_id_seq), 'Order B');

SELECT * FROM orders;

┌──────────────────────────────────┐
│ order_id │ order_name │
├────────────────┼─────────────────┤
1000Order A │
1010Order B │
└──────────────────────────────────┘
Explore Databend Cloud for FREE
Low-cost
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today