RENAME SEQUENCE

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

ALTERSEQUENCEIFEXISTScurrent_nameRENAMETOnew_name

Parameters

ParameterDescription
IF EXISTSRename the sequence only if it exists; if it does not exist, do not return an error.
current_nameThe current name of the sequence you want to modify.
new_nameThe 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.

  1. > SELECT * FROM information_schema.sequences;
  1. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  2. | sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option |
  3. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  4. | def              | db_2            | test_4             | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  5. | def              | test_db         | customer_seq       | INT       |                64 |                       2 |             0 |         101 |                    1 | 9223372036854775807 |         2 | NO           |
  6. | def              | test_db         | desc_customer_list | INT       |                64 |                       2 |             0 |        1000 | -9223372036854775808 |                  -1 |        -2 | NO           |
  7. | def              | test_db         | test_sequence3     | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  8. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  9. (4 rows)
  1. > ALTER SEQUENCE test_db.customer_seq RENAME TO test_db.customer_number;
  1. RENAME SEQUENCE
  1. > SELECT * FROM information_schema.sequences;
  1. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  2. | sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option |
  3. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  4. | def              | db_2            | test_4             | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  5. | def              | test_db         | customer_number    | INT       |                64 |                       2 |             0 |         101 |                    1 | 9223372036854775807 |         2 | NO           |
  6. | def              | test_db         | desc_customer_list | INT       |                64 |                       2 |             0 |        1000 | -9223372036854775808 |                  -1 |        -2 | NO           |
  7. | def              | test_db         | test_sequence3     | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  8. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  9. (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.

  1. > SELECT * FROM information_schema.sequences;
  1. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  2. | sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option |
  3. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  4. | def              | db_2            | test_4             | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  5. | def              | test_db         | customer_number    | INT       |                64 |                       2 |             0 |         101 |                    1 | 9223372036854775807 |         2 | NO           |
  6. | def              | test_db         | desc_customer_list | INT       |                64 |                       2 |             0 |        1000 | -9223372036854775808 |                  -1 |        -2 | NO           |
  7. | def              | test_db         | test_sequence3     | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  8. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  9. (4 rows)
  1. > ALTER SEQUENCE test_db.customer_number RENAME TO db_2.customer_number;
  1. RENAME SEQUENCE
  1. > SELECT * FROM information_schema.sequences;
  1. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  2. | sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option |
  3. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  4. | def              | db_2            | test_4             | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  5. | def              | db_2            | customer_number    | INT       |                64 |                       2 |             0 |         101 |                    1 | 9223372036854775807 |         2 | NO           |
  6. | def              | test_db         | desc_customer_list | INT       |                64 |                       2 |             0 |        1000 | -9223372036854775808 |                  -1 |        -2 | NO           |
  7. | def              | test_db         | test_sequence3     | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  8. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  9. (4 rows)

See also

Was this page helpful?
YesNo