ALTER SEQUENCE

The ALTER SEQUENCE statement applies a schema change to a sequence.

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

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 privilege 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 privilege 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 identifier rules. 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 `unique_rowid()`. They are intended for use in combination with `SERIAL`-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 create a sequence 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, unqualified sequences 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 it:

> 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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Syntax

Parameters

Examples

Change the increment value of a sequence

Rename a sequence

Change the schema of a sequence

See also

​Required privileges

​Syntax

​Parameters

​Examples

​Change the increment value of a sequence

​Rename a sequence

​Change the schema of a sequence

​See also

Required privileges

Syntax

Parameters

Examples

Change the increment value of a sequence

Rename a sequence

Change the schema of a sequence

See also