This page describes how to rename tables and how to add, use, and drop table synonyms.
Options for table renaming and synonyms
You can use the ALTER TABLE
statement to do the following:
- Rename a table and add the old name to a synonym.
- Swap table names.
- Rename a single table.
- Create a new table with a single synonym.
- Add a single synonym to a table without renaming it.
How table renaming with synonyms works
A common scenario is to rename a table and add a synonym that contains the old table name. After renaming the table, you can update applications to use the new name on your schedule. During this period, it's possible that some applications use the old name and others use the new name.
After you update all of your applications to use the new name, we recommend that you remove the synonym. While having a synonym doesn't impact performance, you can't use the old name somewhere else until the synonym is dropped.
Synonyms are stored in the schema as a synonym
object. You can only have one
synonym on a table.
For more information, see Rename a table and add a synonym.
How table name swapping works
When you need to swap names between two tables, you can chain together
RENAME TO
statements to rename two tables in the same statement. This
lets you link applications to a different table without interruptions.
For more information, see Swap table names.
How table renaming works
When you rename a table, Spanner changes the table name in the table's schema. Renaming a table interleaves any child tables with the new table name. Table renaming also changes references to the table for the following:
- Indexes
- Foreign keys
- Change streams
- Fine-grained access control (FGAC)
Spanner doesn't automatically update views to use the new table name.
For more information, see Rename a table.
Table renaming limitations
Table renaming has the following limitations:
- You can't rename a table to the name of a column in that table if the table is interleaved in another table.
- If the table has a view, you might want to drop the view and recreate it after renaming the table.
How synonyms work
You can create a new table with a synonym or alter a table to add a synonym to it without renaming the table. A scenario for when you might want to do this is if you want to use a database for both a production and test environment.
For more information, see Add a synonym to a table.
Permissions
To rename a table or add a synonym to a table, you need the
spanner.databases.updateDdl
permission. To check or edit your permissions, see
Grant permissions to principles.
Rename a table and add a synonym
GoogleSQL
Use ALTER TABLE RENAME TO ADD SYNONYM
to rename a table and add a synonym.
ALTER TABLE table_name RENAME TO new_table_name, ADD SYNONYM table_name;
PostgreSQL
Use ALTER TABLE RENAME WITH ADD SYNONYM
to rename a table and add a synonym.
ALTER TABLE table_name RENAME WITH SYNONYM TO new_table_name;
The following example shows how to rename a table and add a synonym. For example, if you create a table with the following DDL:
GoogleSQL
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
SingerName STRING(1024)
), PRIMARY KEY (SingerId);
PostgreSQL
CREATE TABLE singers (
singer_id BIGINT,
singer_name VARCHAR(1024),
PRIMARY KEY (singer_id));
You can make the following DDL request to rename the table and move the existing
name to the synonym
object.
GoogleSQL
ALTER TABLE Singers RENAME TO SingersNew, ADD SYNONYM Singers;
PostgreSQL
ALTER TABLE singers RENAME WITH SYNONYM TO singers_new;
Swap table names
The following DDL statement changes the names of multiple tables atomically. This is useful when swapping the names between one or more pairs of tables.
GoogleSQL
Use RENAME TABLE
.
RENAME TABLE old_name1 TO new_name1 [,old_name2 TO new_name2 ...];
PostgreSQL
ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name1
RENAME TO new_table_name1
[, ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name2
RENAME TO new_table_name2 ...];
The following example shows how to swap the names of two tables. This requires that the first table is renamed to a temporary name, the second table is renamed to the first table's name, then the first table is renamed to the second table's name.
If you have created two tables as follows:
GoogleSQL
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
SingerName STRING(1024)
), PRIMARY KEY (SingerId);
CREATE TABLE SingersNew (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
MiddleName STRING(1024),
LastName STRING(1024)
), PRIMARY KEY (SingerId);
PostgreSQL
CREATE TABLE singers (
singer_id BIGINT,
singer_name VARCHAR(1024),
PRIMARY KEY (singer_id)
);
CREATE TABLE singers_new (
singer_id BIGINT,
first_name VARCHAR(1024),
middle_name VARCHAR(1024),
last_name VARCHAR(1024)
PRIMARY KEY (singer_id)
);
You can use the following DDL request to swap the table names:
GoogleSQL
RENAME TABLE Singers TO Temp, SingersNew TO Singers, Temp TO SingersNew;
PostgreSQL
ALTER TABLE singers RENAME TO temp,
ALTER TABLE singers_new RENAME TO singers,
ALTER TABLE temp RENAME TO singers_new;
After the DDL statement is applied, the table names are swapped:
GoogleSQL
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
MiddleName STRING(1024),
LastName STRING(1024)
), PRIMARY KEY (SingerId);
CREATE TABLE SingersNew (
SingerId INT64 NOT NULL,
SingerName STRING(1024)
), PRIMARY KEY (SingerId);
PostgreSQL
CREATE TABLE singers (
singer_id BIGINT,
first_name VARCHAR(1024),
middle_name VARCHAR(1024),
last_name VARCHAR(1024)
PRIMARY KEY (singer_id)
);
CREATE TABLE singers_new (
singer_id BIGINT,
singer_name VARCHAR(1024),
PRIMARY KEY (singer_id)
);
Rename a table
To rename a table, use the following syntax:
GoogleSQL
Use either the
ALTER NAME
or RENAME TABLE
statement.
ALTER TABLE table_name RENAME TO new_table_name;
RENAME TABLE table_name TO new_table_name;
PostgreSQL
Use the ALTER TABLE RENAME TO
statement.
ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name
RENAME TO new_table_name;
The following example shows a DDL request that renames the table:
GoogleSQL
RENAME TABLE Singers TO SingersNew;
PostgreSQL
ALTER TABLE singers RENAME TO singers_new;
Add a synonym to a table
To add a synonym to a table:
GoogleSQL
ALTER TABLE table_name ADD SYNONYM synonym;
PostgreSQL
ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name ADD SYNONYM synonym;
The following example shows a DDL request that adds a synonym to the table:
GoogleSQL
ALTER TABLE Singers ADD SYNONYM SingersTest;
PostgreSQL
ALTER TABLE singers ADD SYNONYM singers_test;
Create a table with a synonym
To create a table with a synonym:
GoogleSQL
Use CREATE TABLE SYNONYM synonym_name
.
CREATE TABLE table_name (
...
SYNONYM (synonym)
) PRIMARY KEY (primary_key);
PostgreSQL
Use CREATE TABLE SYNONYM synonym_name
.
CREATE TABLE table_name (
...
SYNONYM (synonym),
PRIMARY KEY (primary_key));
The following example creates a table and adds a synonym.
GoogleSQL
# The table's name is Singers and the synonym is Artists.
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
SingerName STRING(1024),
SYNONYM (Artists)
) PRIMARY KEY (SingerId);
PostgreSQL
# The table's name is singers and the synonym is artists.
CREATE TABLE singers (
singer_id BIGINT,
singer_name VARCHAR(1024),
SYNONYM (artists),
PRIMARY KEY (singer_id));
Remove a synonym from a table
GoogleSQL
Use ALTER TABLE DROP SYNONYM to remove the synonym from the table.
ALTER TABLE table_name DROP SYNONYM synonym;
PostgreSQL
Use ALTER TABLE DROP SYNONYM to remove the synonym from the table.
ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name DROP SYNONYM synonym;
The following example shows a DDL request that drops the synonym from the table:
GoogleSQL
ALTER TABLE Singers DROP SYNONYM SingersTest;
PostgreSQL
ALTER TABLE singers DROP SYNONYM singers_test;