Use ALTER SEQUENCE to change the start value and type of a sequence - PolarDB

ALTER SEQUENCE modifies the properties of an existing sequence or converts it from one sequence type to another.

PolarDB for XScale supports three sequence types: new, group, and time. Each type has different parameter support depending on whether you are modifying its properties in place or converting it to another type.

Usage notes

A group sequence with multiple units cannot be converted to another type, and its UNIT COUNT and INDEX parameters cannot be changed. Attempting to do so causes the sequence to stop working as expected.
Before changing START WITH, analyze the current sequence values and the rate at which values are generated. Setting START WITH too low risks generating duplicate values.
When using the CHANGE TO clause, START WITH is required. Without CHANGE TO, START WITH is optional.

New sequences

ALTER SEQUENCE <name> [ CHANGE TO GROUP | TIME ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameter	Default	Description
`START WITH`	1	The start value for the sequence.
`INCREMENT BY`	1	The step size between consecutive values. Supported only when modifying new sequence properties — not when converting to a group or time sequence.
`MAXVALUE`	9223372036854775807 (signed BIGINT)	The maximum value. Must be a positive integer. Supported only when modifying new sequence properties — not when converting to a group or time sequence.
`CYCLE` \| `NOCYCLE`	`NOCYCLE`	Controls behavior when the maximum value is reached. `CYCLE` restarts the sequence from `START WITH`. `NOCYCLE` returns an error if more values are requested. Supported only when modifying new sequence properties — not when converting to a group or time sequence.

A new sequence can only be converted to a group sequence with a single unit. After converting to a time sequence, the original START WITH value becomes invalid.

Group sequences

ALTER SEQUENCE <name> [ CHANGE TO NEW | TIME ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameter	Default	Description
`START WITH`	1	The start value for the sequence.
`INCREMENT BY`	1	The step size between consecutive values. Supported only when converting a group sequence to a new sequence.
`MAXVALUE`	9223372036854775807 (signed BIGINT)	The maximum value. Must be a positive integer. Supported only when converting a group sequence to a new sequence.
`CYCLE` \| `NOCYCLE`	`NOCYCLE`	Controls behavior when the maximum value is reached. `CYCLE` restarts the sequence from `START WITH`. `NOCYCLE` returns an error if more values are requested. Supported only when converting a group sequence to a new sequence.

When converting a group sequence to a time sequence, the values of START WITH, INCREMENT BY, MAXVALUE, and CYCLE/NOCYCLE become invalid.

Time sequences

ALTER SEQUENCE <name> [ CHANGE TO NEW | GROUP ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameter	Default	Description
`START WITH`	1	The start value for the sequence.
`INCREMENT BY`	1	The step size between consecutive values. Supported only when converting a time sequence to a new sequence.
`MAXVALUE`	9223372036854775807 (signed BIGINT)	The maximum value. Must be a positive integer. Supported only when converting a time sequence to a new sequence.
`CYCLE` \| `NOCYCLE`	`NOCYCLE`	Controls behavior when the maximum value is reached. `CYCLE` restarts the sequence from `START WITH`. `NOCYCLE` returns an error if more values are requested. Supported only when converting a time sequence to a new sequence.

When converting a time sequence to a group sequence, the values of START WITH, INCREMENT BY, MAXVALUE, and CYCLE/NOCYCLE become invalid because the resulting group sequence contains only one unit.

Examples

Change the start value

ALTER SEQUENCE seq1 START WITH 1000000;

Convert to a group sequence

ALTER SEQUENCE seq2 CHANGE TO GROUP START WITH 2000000;

Convert to a time sequence

ALTER SEQUENCE seq3 CHANGE TO TIME;

Convert to a new sequence with default parameters

ALTER SEQUENCE seq4 CHANGE TO NEW START WITH 100;

Convert to a new sequence with explicit parameters

ALTER SEQUENCE seq5 CHANGE TO NEW START WITH 200 INCREMENT BY 2 MAXVALUE 300 NOCYCLE;