The ALTER SEQUENCE statement enables you to alter an existing sequence in a given scope.
Sequences operate most efficiently with an in-memory cache of values. You can specify the size of this cache when you alter the sequence. A block of values is reserved by a node, and requests for values are satisfied from this cache. When exhausted, a new block of values is reserved. Reserving a cached block offers a performance boost, as it enables the Query service to return values directly from memory.
Note however that if a Query node shuts down, or if you alter the sequence, the unused values in the cached block are lost: a new block is reserved when you restart the node, or request the next value. You should choose a cache size with this in mind, along with the expected usage patterns for the sequence.
Sequences are stored in the bucket’s hidden _system scope.
WhWhen you back up a bucket, sequences are included automatically, in accordance with the backup filters.
Similarly, when you restore a bucket, sequences are restored in accordance with the restore command — if you select to restore specific scopes, the sequences associated with those scopes are restored, and no others.
To execute the ALTER SEQUENCE statement, your client must have the Write privilege on the scope.
For more information about access privileges, see clusters:manage-database-users.adoc.
link:partial$grammar/ddl.ebnf[role=include]
The ALTER SEQUENCE statement provides two possible syntaxes for specifying options for a sequence.
| sequence |
(Required) A name that identifies the sequence within a namespace, bucket, and scope. See Sequence Name below. |
| alter-sequence-options |
(Optional) One possible syntax for specifying options for the sequence. See Sequence Options below. |
| sequence-with |
(Optional) The other possible syntax for specifying options for the sequence. See WITH Clause below. |
link:partial$grammar/ddl.ebnf[role=include]
The sequence name specifies the name of the sequence to alter.
Each sequence is associated with a given namespace, bucket, and scope. You must specify the namespace, bucket, and scope to refer to the sequence correctly.
| namespace |
(Optional) The namespace of the bucket which contains the sequence you want to alter. |
| bucket |
(Optional) The bucket which contains the sequence you want to alter. |
| scope |
(Optional) The scope which contains the sequence you want to alter. |
| identifier |
(Required) The name of the sequence. The sequence name is case-sensitive. |
Currently, only the default namespace is available.
If you omit the namespace, the default namespace in the current session is used.
If the query context is set, you can omit the bucket and scope from the statement. In this case, the bucket and scope for the sequence are taken from the query context.
The namespace, bucket, scope, and sequence name must follow the rules for identifiers. If the namespace, bucket, scope, or sequence name contain any special characters such as hyphens (-), you must wrap that part of the expression in backticks (` `).
link:partial$grammar/ddl.ebnf[role=include]
You can use the following optional clauses to specify individual attributes for the sequence. These clauses can occur in any order, but none of them can occur more than once in the statement.
| restart-with |
RESTART WITH Clause [caret down] |
| increment-by |
INCREMENT BY Clause [caret down] |
| maxvalue |
MAXVALUE Clause [caret down] |
| minvalue |
MINVALUE Clause [caret down] |
| cycle |
CYCLE Clause [caret down] |
| cache |
Cache [caret down] |
link:partial$grammar/ddl.ebnf[role=include]
Use the RESTART keyword by itself to restart the sequence from its original starting value.
Use the RESTART WITH clause to restart the sequence from a new value.
If this clause is omitted, the sequence does not restart.
| integer |
(Optional) The restart value for the sequence. |
link:partial$grammar/ddl.ebnf[role=include]
Use the INCREMENT BY clause to specify the increment value of each step in the sequence.
If this clause is omitted, the increment value is not altered.
| integer |
(Required) The step size for the sequence. Use a negative value for a descending sequence. |
link:partial$grammar/ddl.ebnf[role=include]
Use the MAXVALUE clause to specify the maximum value for the sequence.
Use NO MAXVALUE to specify that the maximum value is the highest signed 64-bit integer, 263-1.
If this clause is omitted, the maximum value is not altered.
| integer |
(Optional) The maximum value for the sequence. |
link:partial$grammar/ddl.ebnf[role=include]
Use the MINVALUE clause to specify the minimum value for the sequence.
Use NO MINVALUE to specify that the minimum value is the lowest signed 64-bit integer, -263.
If this clause is omitted, the minimum value is not altered.
| integer |
(Optional) The minimum value for the sequence. |
link:partial$grammar/ddl.ebnf[role=include]
Use the CYCLE clause to specify the whether the sequence should loop when it reaches the maximum or minimum value.
Use NO CYCLE to specify that the sequence should stop when it reaches the maximum or minimum value.
If this clause is omitted, the sequence behavior is not altered.
link:partial$grammar/ddl.ebnf[role=include]
Use the CACHE clause to specify the cache size for the sequence.
Use NO CACHE to specify a cache size of 1.
If this clause is omitted, the cache size is not altered.
| integer |
(Optional) The cache size for the sequence.
The value must be greater than |
link:partial$grammar/ddl.ebnf[role=include]
You can use the WITH clause to specify options for the sequence using a JSON object.
| expr |
(Required) An object with the following properties. |
| Name | Description | Schema |
|---|---|---|
restart |
The restart value for the sequence. If unspecified, the sequence does not restart. |
Integer |
increment |
The step size for the sequence. Use a negative value for a descending sequence. |
Integer |
max |
The maximum value for the sequence. |
Integer (int64) |
min |
The minimum value for the sequence. |
Integer (int64) |
cycle |
Whether the sequence should continue when it reaches the specified maximum value or minimum value. |
Boolean |
cache |
The cache size for the sequence.
The value must be greater than |
Integer |
ROOT:partial$query-context.adoc
This example assumes that you have created a sequence as follows.
CREATE SEQUENCE seq3 IF NOT EXISTS START WITH 5 INCREMENT BY 5 MAXVALUE 1000 MINVALUE 0 CYCLE;The following statement restarts the sequence at the original starting value.
ALTER SEQUENCE seq3 RESTART;The following query tests the sequence.
SELECT NEXT VALUE FOR seq3;The query returns the specified starting value, 5.
[
{
"$1": 5
}
]This example assumes that you have created a sequence as follows.
CREATE SEQUENCE seq3 IF NOT EXISTS START WITH 5 INCREMENT BY 5 MAXVALUE 1000 MINVALUE 0 CYCLE;The following statement restarts the sequence at a new starting value.
ALTER SEQUENCE seq3 RESTART WITH 25;The following query tests the sequence.
SELECT NEXT VALUE FOR seq3;The query returns the specified starting value, 25.
[
{
"$1": 25
}
]This example assumes that you have created a sequence as follows.
CREATE SEQUENCE seq3 IF NOT EXISTS START WITH 5 INCREMENT BY 5 MAXVALUE 1000 MINVALUE 0 CYCLE;The following statement alters the increment and maximum value of the sequence.
ALTER SEQUENCE seq3 INCREMENT BY 1 MAXVALUE 250;This example assumes that you have created a descending sequence as follows.
CREATE SEQUENCE seq4 IF NOT EXISTS WITH {"start": 10, "increment": -1, "min": 0};The following statement specifies that the sequence should count down from 10 again when it reaches 0.
ALTER SEQUENCE seq4 WITH {"max": 10, "cycle": true};Note that you must specify the maximum value to be 10, even though the starting value is already set.
If you didn’t do this, the sequence would cycle to the highest possible value, 263-1.
-
To create a sequence, see n1ql-language-reference/createsequence.adoc.
-
To drop a sequence, see n1ql-language-reference/dropsequence.adoc.
-
To use a sequence in an expression, see n1ql-language-reference/sequenceops.adoc.
-
To monitor sequences, see Monitor Sequences.