On this page 
New in v2.0: The ALTER SEQUENCE statement changes the name, increment values, and other settings of a sequence.
Note:
To understand how CockroachDB changes schema elements without requiring table locking or other user-visible downtime, see Online Schema Changes in CockroachDB.Required Privileges
The user must have the CREATE privilege on the parent database.
Synopsis
Parameters
| Parameter | Description |
|---|---|
IF EXISTS |
Modify the sequence only if it exists; if it does not exist, do not return an error. |
sequence_name |
The name of the sequence you want to modify. |
INCREMENT |
The new value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence. |
MINVALUE |
The new minimum value of the sequence. Default: 1 |
MAXVALUE |
The new maximum value of the sequence. Default: 9223372036854775807 |
START |
The value the sequence starts at if you RESTART or if the sequence hits the MAXVALUE and CYCLE is set. RESTART and CYCLE are not implemented yet. |
CYCLE |
The sequence will wrap around when the sequence value hits the maximum or minimum value. If NO CYCLE is set, the sequence will not wrap. |
Examples
Change the Increment Value of a Sequence
In this example, we're going to change the increment value of a sequence from its current state (i.e., 1) to 2.
> ALTER SEQUENCE customer_seq INCREMENT 2;
Next, we'll add another record to the table and check that the new record adheres to the new sequence.
> INSERT INTO customer_list (customer, address) VALUES ('Marie', '333 Ocean Ave');
> SELECT * FROM customer_list;
+----+----------+--------------------+
| id | customer | address |
+----+----------+--------------------+
| 1 | Lauren | 123 Main Street |
| 2 | Jesse | 456 Broad Ave |
| 3 | Amruta | 9876 Green Parkway |
| 5 | Marie | 333 Ocean Ave |
+----+----------+--------------------+
Set the Next Value of a Sequence
In this example, we're going to change the next value of the example sequence (customer_seq). Currently, the next value will be 7 (i.e., 5 + INCREMENT 2). We will change the next value to 20.
Note:
You cannot set a value outside the MAXVALUE or MINVALUE of the sequence. > SELECT setval('customer_seq', 20, false);
+--------+
| setval |
+--------+
| 20 |
+--------+
Note:
The setval('seq_name', value, is_called) function in CockroachDB SQL mimics the setval() function in PostgreSQL, but it does not store the is_called flag. Instead, it sets the value to val - increment for false or val for true. Let's add another record to the table to check that the new record adheres to the new next value.
> INSERT INTO customer_list (customer, address) VALUES ('Lola', '333 Schermerhorn');
+----+----------+--------------------+
| id | customer | address |
+----+----------+--------------------+
| 1 | Lauren | 123 Main Street |
| 2 | Jesse | 456 Broad Ave |
| 3 | Amruta | 9876 Green Parkway |
| 5 | Marie | 333 Ocean Ave |
| 20 | Lola | 333 Schermerhorn |
+----+----------+--------------------+
See Also
Was this helpful?
