ALTER SEQUENCE

On this page

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

Note:

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 have the CREATE privilege on the parent database.
To change the schema of a sequence with ALTER SEQUENCE ... SET SCHEMA, or to change the database of a sequence with ALTER SEQUENCE ... RENAME TO, the user must also have the DROP privilege on the sequence.

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. Note that `RENAME TO` can be used to move a sequence from one database to another, but it cannot be used to move a sequence from one schema to another. To change a sequence's schema, use `ALTER SEQUENCE ...SET SCHEMA` instead. In a future release, `RENAME TO` will be limited to changing the name of a sequence, and will not have to the ability to change a sequence's database.
`CYCLE`/`NO CYCLE`	The sequence will wrap around when the sequence value hits the maximum or minimum value. 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`	The value the sequence starts at if you `RESTART` or if the sequence hits the `MAXVALUE` and `CYCLE` is set. `RESTART` and `CYCLE` are not implemented yet.
`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 database of a sequence

In this example, we will move the sequence we renamed in the first example (even_sequence) from defaultdb (i.e., the default database) to a different database.

> SHOW SEQUENCES FROM defaultdb;

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

> CREATE DATABASE mydb;

> ALTER SEQUENCE even_sequence RENAME TO mydb.even_sequence;

> SHOW SEQUENCES FROM defaultdb;

  sequence_schema | sequence_name
------------------+----------------
(0 rows)

> SHOW SEQUENCES FROM mydb;

  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)

Pricing

Contact us

Sign In