ALTER SEQUENCE

Synopsis

Use the ALTER SEQUENCE statement to change the definition of an existing sequence in the current schema.

Syntax

• Grammar
• Diagram

alter_sequence ::= ALTER SEQUENCE [ IF EXISTS ] 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 ]

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.

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)

See also

• CREATE SEQUENCE
• DROP SEQUENCE
• currval()
• lastval()
• nextval()