ALTER SEQUENCE

The ALTER SEQUENCE applies a to a sequence.

The ALTER SEQUENCE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

• To alter a sequence, the user must be the owner of the sequence.
• To change the schema of a sequence with ALTER SEQUENCE ... SET SCHEMA, the user must have the DROP on the sequence and the CREATE privilege on the new schema.
• To rename a sequence with ALTER SEQUENCE ... RENAME TO, the user must have the DROP on the sequence and the CREATE privilege on the sequence’s database.

Syntax

Parameters

Parameter	Description
IF EXISTS	Modify the sequence only if it exists; if it does not exist, do not return an error.
sequence\_name	The name of the sequence.
RENAME TO sequence\_name	Rename the sequence to sequence\_name, which must be unique to its database and follow these . Name changes do not propagate to the table(s) using the sequence. RENAME TO only changes the name of the sequence; it cannot move the sequence to a different database or schema. To change a sequence’s schema, use ALTER SEQUENCE ... SET SCHEMA instead.
CYCLE/NO CYCLE	The sequence will wrap around when the sequence value reaches the maximum or minimum value. CYCLE is not implemented. If NO CYCLE is set, the sequence will not wrap.
OWNED BY column\_name	Associates the sequence to a particular column. If that column or its parent table is dropped, the sequence will also be dropped. Specifying an owner column with OWNED BY replaces any existing owner column on the sequence. To remove existing column ownership on the sequence and make the column free-standing, specify OWNED BY NONE. Default:NONE
	CACHE	The number of sequence values to cache in memory for reuse in the session. A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid. Default:1 (sequences are not cached by default)
MINVALUE	The new minimum value of the sequence. Default:1
MAXVALUE	The new maximum value of the sequence. Default:9223372036854775807
INCREMENT	The new value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.
START [WITH]	The value the sequence starts at if you RESTART or if the sequence reaches MAXVALUE and CYCLE is set.
RESTART [WITH]	Sets nextval to the specified number, or back to the original START value.
VIRTUAL	Creates a virtual sequence. Virtual sequences are sequences that do not generate monotonically increasing values and instead produce values like those generated by the built-in function . They are intended for use in combination with -typed columns.
SET SCHEMA schema\_name	Change the schema of the sequence to schema\_name.
OWNER TO role\_spec	Change the owner of the sequence to role\_spec.

Examples

Change the increment value of a sequence

In this example, we’re going to change the increment value of a sequence from its current state (i.e., 1) to 2.

> CREATE SEQUENCE customer_seq;

> SHOW CREATE customer_seq;

   table_name  |                                     create_statement
---------------+-------------------------------------------------------------------------------------------
  customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)

> ALTER SEQUENCE customer_seq INCREMENT 2;

   table_name  |                                        create_statement
---------------+--------------------------------------------------------------------------------------------------
  customer_seq | CREATE SEQUENCE public.customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 2 START 1
(1 row)

Rename a sequence

In this example, we will change the name of sequence.

> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;

> SHOW SEQUENCES;

  sequence_schema | sequence_name
------------------+----------------
  public          | even_numbers
(1 row)

> ALTER SEQUENCE even_numbers RENAME TO even_sequence;

> SHOW SEQUENCES;

  sequence_schema | sequence_name
------------------+----------------
  public          | even_sequence
(1 row)

Change the schema of a sequence

Suppose you that you would like to add to a new schema called cockroach_labs:

> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;

> SHOW SEQUENCES;

  sequence_schema | sequence_name
------------------+----------------
  public          | even_numbers
(1 row)

By default, created in the database belong to the public schema:

> SHOW CREATE public.even_numbers;

      table_name      |                                        create_statement
----------------------+--------------------------------------------------------------------------------------------------
  public.even_numbers | CREATE SEQUENCE public.even_numbers MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 2 START 2
(1 row)

If the new schema does not already exist, :

> CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the sequence’s schema:

> ALTER SEQUENCE even_numbers SET SCHEMA cockroach_labs;

> SHOW CREATE public.even_numbers;

ERROR: relation "public.even_numbers" does not exist
SQLSTATE: 42P01

> SHOW SEQUENCES;

  sequence_schema | sequence_name
------------------+----------------
  cockroach_labs  | even_numbers
(1 row)

> SHOW CREATE cockroach_labs.even_numbers;

          table_name          |                                            create_statement
------------------------------+----------------------------------------------------------------------------------------------------------
  cockroach_labs.even_numbers | CREATE SEQUENCE cockroach_labs.even_numbers MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 2 START 2
(1 row)

See also