On this page
Warning:
As of November 12, 2021, CockroachDB v20.1 is no longer supported. For more details, refer to the Release Support Policy.
The RENAME TO
statement is part of ALTER SEQUENCE
, and changes the name of a sequence.
Warning:
You cannot rename a sequence that's being used in a table. To rename the sequence, drop the DEFAULT
expressions that reference the sequence, rename the sequence, and add the DEFAULT
expressions back.Note:
To understand how CockroachDB changes schema elements without requiring table locking or other user-visible downtime, see Online Schema Changes in CockroachDB.Required privileges
The user must have the CREATE
privilege on the parent database.
Synopsis
Parameters
Parameter | Description |
---|---|
IF EXISTS |
Rename the sequence only if it exists; if it does not exist, do not return an error. |
current_name |
The current name of the sequence you want to modify. |
new_name |
The new name of the sequence, which must be unique to its database and follow these identifier rules. Name changes do not propagate to the table(s) using the sequence. |
Examples
Rename a Sequence
In this example, we will change the name of sequence customer_seq
to customer_number
.
> SELECT * FROM information_schema.sequences;
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | test_db | customer_seq | INT | 64 | 2 | 0 | 101 | 1 | 9223372036854775807 | 2 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)
> ALTER SEQUENCE test_db.customer_seq RENAME TO test_db.customer_number;
RENAME SEQUENCE
> SELECT * FROM information_schema.sequences;
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | test_db | customer_number | INT | 64 | 2 | 0 | 101 | 1 | 9223372036854775807 | 2 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)
Move a Sequence
In this example, we will move the sequence we renamed in the first example (customer_number
) to a different database.
> SELECT * FROM information_schema.sequences;
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | test_db | customer_number | INT | 64 | 2 | 0 | 101 | 1 | 9223372036854775807 | 2 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)
> ALTER SEQUENCE test_db.customer_number RENAME TO db_2.customer_number;
RENAME SEQUENCE
> SELECT * FROM information_schema.sequences;
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | db_2 | customer_number | INT | 64 | 2 | 0 | 101 | 1 | 9223372036854775807 | 2 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)