> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# ALTER SEQUENCE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `ALTER SEQUENCE` statement applies a schema change to a sequence.
The \`\` 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`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE customer_seq;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE customer_seq;
```
```
table_name | create_statement
---------------+-------------------------------------------------------------------------------------------
customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SEQUENCES;
```
```
sequence_schema | sequence_name
------------------+----------------
public | even_numbers
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER SEQUENCE even_numbers RENAME TO even_sequence;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SEQUENCE even_numbers INCREMENT 2 START 2;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SEQUENCES;
```
```
sequence_schema | sequence_name
------------------+----------------
public | even_numbers
(1 row)
```
By default, unqualified sequences created in the database belong to the `public` schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA IF NOT EXISTS cockroach_labs;
```
Then, change the sequence's schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER SEQUENCE even_numbers SET SCHEMA cockroach_labs;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE public.even_numbers;
```
```
ERROR: relation "public.even_numbers" does not exist
SQLSTATE: 42P01
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SEQUENCES;
```
```
sequence_schema | sequence_name
------------------+----------------
cockroach_labs | even_numbers
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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
* SQL Statements
* Online Schema Changes