ALTER SEQUENCE
Synopsis
Use the ALTER SEQUENCE statement to change the definition of a sequence in the current schema.
Syntax
Grammar
Diagramalter_sequence ::= ALTER SEQUENCE [ IF EXISTS ] sequence_name
alter_sequence_options
alter_sequence_options ::= [ AS seq_data_type ]
[ INCREMENT [ BY ] int_literal ]
[ MINVALUE int_literal | NO MINVALUE ]
[ MAXVALUE int_literal | NO MAXVALUE ]
[ START [ WITH ] int_literal ]
[ RESTART [ [ WITH ] int_literal ] ]
[ CACHE int_literal ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name . column_name
| NONE } ]
[ SET { LOGGED
| UNLOGGED
| SCHEMA schema_name } ]
[ OWNER TO { new_owner
| CURRENT_USER
| SESSION_USER } ]
[ RENAME TO new_name ]
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 seq_data_type
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 int_literal
Specify the difference between consecutive values in the sequence. Default is 1.
MINVALUE int_literal | 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 int_literal | 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⁶³-1.
START WITH int_literal
Specify the first value in the sequence. start cannot be less than minvalue. Default is 1.
RESTART [ [ WITH ] int_literal ] ]
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 int_literal
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.
[ NO ] CYCLE
If CYCLE is specified, the sequence will wrap around once it has reached minvalue or maxvalue. If maxvalue was reached, minvalue will be the next number in the sequence. If minvalue was reached (for a descending sequence), maxvalue will be the next number in a sequence. NO CYCLE is the default.
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.
SET LOGGED | UNLOGGED
Change the sequence from unlogged to logged or vice-versa. Cannot be applied to a temporary sequence.
Currently the UNLOGGED option is ignored. It's handled as LOGGED default persistence.
SET SCHEMA schema_name
Change the schema of the sequence.
OWNER TO new_owner | CURRENT_USER | SESSION_USER
Change the owner of the schema.
CURRENT_USER - username of current execution context.
SESSION_USER - username of current session.
RENAME TO new_name
Rename the sequence to the specified sequence name.
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)