The RENAME TABLE
statement changes the name of a table. It can also be used to move a table from one database to another.
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
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 New in v1.1
Whenever you initiate a schema change, CockroachDB registers it as a job, which you can view with SHOW JOBS
.
Examples
Rename a table
> SHOW TABLES FROM db1;
+--------+
| Table |
+--------+
| table1 |
| table2 |
+--------+
> ALTER TABLE db1.table1 RENAME TO db1.tablea
> SHOW TABLES FROM db1;
+--------+
| Table |
+--------+
| table2 |
| tablea |
+--------+
To avoid an error in case the table does not exist, you can include IF EXISTS
:
> ALTER TABLE IF EXISTS db1.table1 RENAME TO db1.table2;
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
:
> SHOW DATABASES;
+----------+
| Database |
+----------+
| db1 |
| db2 |
| system |
+----------+
> SHOW TABLES FROM db1;
+--------+
| Table |
+--------+
| table2 |
| tablea |
+--------+
> SHOW TABLES FROM db2;
+-------+
| Table |
+-------+
+-------+
> ALTER TABLE db1.tablea RENAME TO db2.tablea
> SHOW TABLES FROM db1;
+--------+
| Table |
+--------+
| table2 |
+--------+
> SHOW TABLES FROM db2;
+--------+
| Table |
+--------+
| tablea |
+--------+