On this page
The RENAME TO statement is part of ALTER TABLE, and changes the name of a table.
Note:
ALTER TABLE ... RENAME TO cannot be used to move a table from one schema to another. To change a table's schema, use SET SCHEMA.
ALTER TABLE ... RENAME TO cannot be used to move a table from one database to another. To change a table's database, use BACKUP and RESTORE.
Note:
It is not possible to rename a table referenced by a view. For more details, see View Dependencies.
Note:
The ALTER TABLE ... RENAME TO 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
ALTERTABLEIFEXISTScurrent_nameRENAMETOtable_name
Parameters
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
Rename a table
icon/buttons/copy
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
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;
See also
- CREATE TABLE
- ALTER TABLE
- SHOW TABLES
- DROP TABLE
- SHOW JOBS
- SQL Statements
- Online Schema Changes
Was this helpful?
MySQL offers two ways to rename tables. The first one uses the ALTER TABLE syntax:
ALTER TABLE old_table_name RENAME new_table_name;
The second way is to use RENAME TABLE:
RENAME TABLE old_table_name TO new_table_name;
RENAME TABLE offers more flexibility. It allows renaming multiple tables in one statement. This can be useful when replacing a table with a new pre-populated version:
RENAME TABLE products TO products_old, products_new TO products;
The above statement is executed left to right, so there's no conflict naming products_new to products since the existing table has already been renamed to products_old. Furthermore, this is done atomically.