> ## 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 at the node level. All sessions on the node share the same cache, which can be concurrently accessed, and which reduces the chance of creating large gaps between generated IDs. 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). While this parameter caches sequences per node, to cache sequence values per session, use [`PER SESSION CACHE`](#per-session-cache) explicitly. |
| `PER NODE CACHE` | The number of sequence values to cache in memory at the node level. All sessions on the node share the same cache, which can be concurrently accessed, and which reduces the chance of creating large gaps between generated IDs. A cache size of `1` means that there is no cache, and cache sizes of less than `1` are not valid. This is the default caching strategy, and is equivalent to specifying `CACHE`. |
| `PER SESSION CACHE` | The number of sequence values to cache in memory for reuse within a single session. A cache size of `1` means that there is no cache, and cache sizes of less than `1` are not valid. |
| `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)
```
## Known limitations
* Altering the minimum or maximum value of a series does not check the current value of a series. This means that it is possible to silently set the maximum to a value less than, or a minimum value greater than, the current value.
## See also
* `CREATE SEQUENCE`
* `DROP SEQUENCE`
* `SHOW SEQUENCES`
* Functions and Operators
* SQL Statements
* Online Schema Changes