Modifies the definition of a sequence generator.
Syntax
ALTER SEQUENCE name [ INCREMENT BY increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ CACHE cache | NOCACHE ] [ CYCLE ]Description
ALTER SEQUENCE modifies the parameters of an existing sequence generator. Any parameter you omit retains its current setting.
Parameters
| Parameter | Default behavior | Description |
|---|---|---|
name | — | Name of the sequence to modify. Can be schema-qualified. |
INCREMENT BY increment | Retains current increment | Step value for the sequence. A positive value creates an ascending sequence; a negative value creates a descending sequence. |
MINVALUE minvalue | Retains current minimum | Minimum value the sequence can generate. |
MAXVALUE maxvalue | Retains current maximum | Maximum value the sequence can generate. |
CACHE cache | NOCACHE | Retains current cache setting | Number of sequence values to preallocate in memory. Minimum is 1, which generates one value at a time. |
CYCLE | Retains current cycle setting | Allows the sequence to wrap around when it reaches its limit. For an ascending sequence, the next value after the maximum is MINVALUE. For a descending sequence, the next value after the minimum is MAXVALUE. |
Usage notes
Changes are immediate and irreversible. To prevent blocking concurrent transactions that retrieve values from the same sequence, ALTER SEQUENCE does not support rollback.
Cache affects when changes take effect across backends. The current backend sees the new parameters immediately. Other backends continue using preallocated NEXTVAL values until their cache is exhausted, then pick up the new parameters.
Oracle compatibility
The following keywords are available in PolarDB for Oracle but are not compatible with Oracle databases:
| Keyword | Behavior |
|---|---|
NO MINVALUE | Sets MINVALUE to 1 for ascending sequences and -2^63-1 for descending sequences. |
NO MAXVALUE | Sets MAXVALUE to 2^63-1 for ascending sequences and -1 for descending sequences. |
NO CYCLE | Specifies that the sequence does not wrap around. |
Example
Set the increment to 2 and preallocate 5 values for the sequence serial:
ALTER SEQUENCE serial INCREMENT BY 2 CACHE 5;