ALTER SEQUENCE
Synopsis
Use the ALTER SEQUENCE statement to change the definition of a sequence in the current schema.
Syntax
alter_sequence ::= ALTER SEQUENCE [ IF EXISTS ] sequence_name
alter_sequence_options
name ::= '<Text Literal>'
alter_sequence_options ::= [ AS seq_data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ]
[ OWNED BY table_name.table_column | NONE ]
alter_sequence
name
alter_sequence_options
Semantics
alter_sequence
ALTER SEQUENCE sequence_name [ IF EXISTS ]
Specify the name of the sequence (sequence_name). An error is raised if a sequence with that name does not exists in the current schema and IF EXISTS is not specified.
sequence_options
AS datatype
Changes the data type of a sequence. This automatically changes the minimum and maximum values of the sequence if the previous values were beyond what the new type allows. Valid types are smallint, integer, and bigint.
INCREMENT BY increment
Specify the difference between consecutive values in the sequence. Default is 1.
MINVALUE minvalue | NO MINVALUE
Specify the minimum value allowed in the sequence. If this value is reached (in a sequence with a negative increment), nextval() will return an error. If NO MINVALUE is specified, the default value will be used. Default is 1.
MAXVALUE maxvalue | NO MAXVALUE
Specify the maximum value allowed in the sequence. If this value is reached, nextval() will return an error. If NO MAXVALUE is specified, the default will be used. Default is 2<sup>63</sup> - 1.
START WITH start
Specify the first value in the sequence. start cannot be less than minvalue. Default is 1.
RESTART [ [ WITH ] restart ] ]
Change the current value of the sequence. If no value is specified, the current value will be set to the last value specified with START [ WITH ] when the sequence was created or altered.
CACHE cache
Specify how many numbers from the sequence to cache in the client. Default is 1.
When YB-TServer ysql_sequence_cache_minval configuration flag is not explicitly turned off (set to 0), the maximum value of the flag and the cache clause will be used.
OWNED BY table_name.table_column | NONE
It gives ownership of the sequence to the specified column (if any). This means that if the column (or the table to which it belongs to) is dropped, the sequence will be automatically dropped. If NONE is specified, any previous ownership will be deleted.
Examples
Create a simple sequence.
yugabyte=# CREATE SEQUENCE s;
CEATE SEQUENCE
Modify the increment value.
yugabyte=# ALTER SEQUENCE s INCREMENT BY 5;
ALTER SEQUENCE
yugabyte=# SELECT nextval('s');
nextval
---------
1
(1 row)
yugabyte=# SELECT nextval('s');
nextval
---------
6
(1 row)
Modify the starting value.
yugabyte=# ALTER SEQUENCE s RESTART WITH 2;
ALTER SEQUENCE
yugabyte=# SELECT nextval('s');
nextval
---------
2
(1 row)
yugabyte=# SELECT nextval('s');
nextval
---------
7
(1 row)