alter-sequence.md

title	summary	toc
ALTER SEQUENCE	Use the ALTER SEQUENCE statement to change the name, increment values, and other settings of a sequence.	true

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

{% include {{{ page.version.version }}/misc/schema-change-stmt-note.md %}

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

{% include {{ page.version.version }}/sql/generated/diagrams/alter_sequence.html %}

Parameters

<style> table td:first-child { min-width: 225px; } </style>

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.

{% include copy-clipboard.html %}

> CREATE SEQUENCE customer_seq;

{% include copy-clipboard.html %}

> SHOW CREATE customer_seq;

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;

{% include copy-clipboard.html %}

> SHOW SEQUENCES;

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

{% include copy-clipboard.html %}

> ALTER SEQUENCE even_numbers RENAME TO even_sequence;

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

> SHOW SEQUENCES FROM defaultdb;

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

{% include copy-clipboard.html %}

> CREATE DATABASE mydb;

{% include copy-clipboard.html %}

> ALTER SEQUENCE even_sequence RENAME TO mydb.even_sequence;

{% include copy-clipboard.html %}

> SHOW SEQUENCES FROM defaultdb;

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;

{% include copy-clipboard.html %}

> SHOW SEQUENCES;

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

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

> CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the sequence's schema:

{% include copy-clipboard.html %}

> ALTER SEQUENCE even_numbers SET SCHEMA cockroach_labs;

{% include copy-clipboard.html %}

> SHOW CREATE public.even_numbers;

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

{% include copy-clipboard.html %}

> SHOW SEQUENCES;

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

{% include copy-clipboard.html %}

> 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

• CREATE SEQUENCE
• DROP SEQUENCE
• SHOW SEQUENCES
• Functions and Operators
• Other SQL Statements
• Online Schema Changes