ALTER TABLE ... RENAME TO

On this page Carat arrow pointing down
Warning:
CockroachDB v21.1 is no longer supported as of November 18, 2022. For more details, refer to the Release Support Policy.

The RENAME TO statement is part of ALTER TABLE, and changes the name of a table.

Note:

ALTER TABLE ... RENAME TO can be used to move a table from one database to another, but it cannot be used to move a table from one schema to another. To change a table's schema, use SET SCHEMA.

Note that, in a future release, ALTER TABLE ... RENAME TO will be limited to changing the name of a table, and will not have to the ability to change a table's database.

Note:

It is not possible to rename a table referenced by a view. For more details, see View Dependencies.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

The user must have the DROP privilege on the table and the CREATE on the parent database. When moving a table from one database to another, the user must have the CREATE privilege on both the source and target databases.

Synopsis

ALTER TABLE IF EXISTS current_name RENAME TO new_name

Parameters

Parameter Description
IF EXISTS Rename the table only if a table with the current name exists; if one does not exist, do not return an error.
current_name The current name of the table.
new_name The new name of the table, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as database.name.

The UPSERT and INSERT ON CONFLICT statements use a temporary table called excluded to handle uniqueness conflicts during execution. It's therefore not recommended to use the name excluded for any of your tables.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

icon/buttons/copy
$ cockroach demo

Rename a table

icon/buttons/copy
> SHOW TABLES;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
icon/buttons/copy
> ALTER TABLE users RENAME TO riders;
icon/buttons/copy
> SHOW TABLES;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | riders                     | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)

To avoid an error in case the table does not exist, you can include IF EXISTS:

icon/buttons/copy
> ALTER TABLE IF EXISTS customers RENAME TO clients;

Move a table

To move a table from one database to another, use the above syntax but specify the source database after ALTER TABLE and the target database after RENAME TO:

icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | riders                     | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
icon/buttons/copy
> SHOW TABLES FROM defaultdb;
  schema_name | table_name | type | estimated_row_count
--------------+------------+------+----------------------
(0 rows)
icon/buttons/copy
> ALTER TABLE movr.promo_codes RENAME TO defaultdb.promos;
icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | riders                     | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(5 rows)
icon/buttons/copy
> SHOW TABLES FROM defaultdb;
  schema_name | table_name | type  | estimated_row_count
--------------+------------+-------+----------------------
  public      | promos     | table |                1000
(1 row)

See also


Yes No
On this page

Yes No