CREATE SEQUENCE
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> ]
Parameter | Description | Default |
---|---|---|
<sequence> | The name of the sequence to be created. | - |
START | The initial value of the sequence. | 1 |
INCREMENT | The increment value for each call to NEXTVAL. | 1 |
Access control requirements
Privilege | Object Type | Description |
---|---|---|
CREATE SEQUENCE | Global | Creates a sequence. |
To create a sequence, the user performing the operation or the current_role must have the CREATE SEQUENCE privilege.
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 │
├────────────────┼─────────────────┤
│ 1000 │ Order A │
│ 1010 │ Order B │
└──────────────────────────────────┘