New in v2.0: The ALTER SEQUENCE
statement changes the name, increment values, and other settings of a sequence.
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
.
MAXVALUE
or MINVALUE
of the sequence. > SELECT setval('customer_seq', 20, false);
+--------+
| setval |
+--------+
| 20 |
+--------+
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 |
+----+----------+--------------------+