This page describes how to rename tables and how to add, use, and drop tablesynonyms in GoogleSQL-dialect databases and PostgreSQL-dialect databases.

Options for table renaming and synonyms

You can use theALTER 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 oldtable name. After renaming the table, you can update applications to use the newname on your schedule. During this period, it's possible that some applicationsuse the old name and others use the new name.

After you update all of your applications to use the new name, we recommend thatyou remove the synonym. While having a synonym doesn't impact performance, youcan't use the old name somewhere else until the synonym is dropped.

Synonyms are stored in the schema as asynonym object. You can only have onesynonym on a table.

For more information, seeRename a table and add a synonym.

How table name swapping works

When you need to swap names between two tables, you can chain togetherRENAME TO statements to rename two tables in the same statement. Thislets you link applications to a different table without interruptions.

For more information, seeSwap table names.

How table renaming works

When you rename a table, Spanner changes the table name in thetable's schema. Renaming a table interleaves any child tables with the new tablename. 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, seeRename 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 isinterleaved in another table.
• You can't rename indexes. To change the name of an index, drop it and recreatethe index with a new name.
• If the table has a view, you might want to drop the view and recreate it afterrenaming the table.

How synonyms work

You can create a new table with a synonym or alter a table to add a synonym toit without renaming the table. A scenario for when you might want to do thisis if you want to use a database for both a production and test environment.

For more information, seeAdd a synonym to a table.

Permissions

To rename a table or add a synonym to a table, you need thespanner.databases.updateDdl permission. To check or edit your permissions, seeGrant permissions to principles.

Rename a table and add a synonym

ALTERTABLEtable_nameRENAMETOnew_table_name,ADDSYNONYMtable_name;

ALTERTABLEtable_nameRENAMEWITHSYNONYMTOnew_table_name;

The following example shows how to rename a table and add a synonym. Forexample, if you create a table with the following DDL:

CREATETABLESingers(SingerIdINT64NOTNULL,SingerNameSTRING(1024)),PRIMARYKEY(SingerId);

CREATETABLEsingers(singer_idBIGINT,singer_nameVARCHAR(1024),PRIMARYKEY(singer_id));

You can make the following DDL request to rename the table and move the existingname to thesynonym object.

ALTERTABLESingersRENAMETOSingersNew,ADDSYNONYMSingers;

ALTERTABLEsingersRENAMEWITHSYNONYMTOsingers_new;

Swap table names

RENAMETABLEold_name1TOnew_name1[,old_name2TOnew_name2...];

ALTERTABLE[IFEXISTS][ONLY]table_name1RENAMETOnew_table_name1[,ALTERTABLE[IFEXISTS][ONLY]table_name2RENAMETOnew_table_name2...];

The following example shows how to swap the names of two tables. This requiresthat the first table is renamed to a temporary name, the second table is renamedto the first table's name, then the first table is renamed to the second table'sname.

If you have created two tables as shown in the following:

CREATETABLESingers(SingerIdINT64NOTNULL,SingerNameSTRING(1024)),PRIMARYKEY(SingerId);CREATETABLESingersNew(SingerIdINT64NOTNULL,FirstNameSTRING(1024),MiddleNameSTRING(1024),LastNameSTRING(1024)),PRIMARYKEY(SingerId);

CREATETABLEsingers(singer_idBIGINT,singer_nameVARCHAR(1024),PRIMARYKEY(singer_id));CREATETABLEsingers_new(singer_idBIGINT,first_nameVARCHAR(1024),middle_nameVARCHAR(1024),last_nameVARCHAR(1024)PRIMARYKEY(singer_id));

You can use the following DDL request to swap the table names:

RENAMETABLESingersTOTemp,SingersNewTOSingers,TempTOSingersNew;

ALTERTABLEsingersRENAMETOtemp,ALTERTABLEsingers_newRENAMETOsingers,ALTERTABLEtempRENAMETOsingers_new;

After the DDL statement is applied, the table names are swapped, as shown in thefollowing:

CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),MiddleNameSTRING(1024),LastNameSTRING(1024)),PRIMARYKEY(SingerId);CREATETABLESingersNew(SingerIdINT64NOTNULL,SingerNameSTRING(1024)),PRIMARYKEY(SingerId);

CREATETABLEsingers(singer_idBIGINT,first_nameVARCHAR(1024),middle_nameVARCHAR(1024),last_nameVARCHAR(1024)PRIMARYKEY(singer_id));CREATETABLEsingers_new(singer_idBIGINT,singer_nameVARCHAR(1024),PRIMARYKEY(singer_id));

Rename a table

ALTERTABLEtable_nameRENAMETOnew_table_name;RENAMETABLEtable_nameTOnew_table_name;

ALTERTABLE[IFEXISTS][ONLY]table_nameRENAMETOnew_table_name;

The following example shows a DDL request that renames the table:

RENAMETABLESingersTOSingersNew;

ALTERTABLEsingersRENAMETOsingers_new;

Add a synonym to a table

To add a synonym to a table:

ALTERTABLEtable_nameADDSYNONYMsynonym;

ALTERTABLE[IFEXISTS][ONLY]table_nameADDSYNONYMsynonym;

The following example shows a DDL request that adds a synonym to the table:

ALTERTABLESingersADDSYNONYMSingersTest;

ALTERTABLEsingersADDSYNONYMsingers_test;

Create a table with a synonym

To create a table with a synonym:

CREATETABLEtable_name(...SYNONYM(synonym))PRIMARYKEY(primary_key);

CREATETABLEtable_name(...SYNONYM(synonym),PRIMARYKEY(primary_key));

The following example creates a table and adds a synonym.

# The table's name is Singers and the synonym is Artists.CREATETABLESingers(SingerIdINT64NOTNULL,SingerNameSTRING(1024),SYNONYM(Artists))PRIMARYKEY(SingerId);

#Thetable'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

ALTERTABLEtable_nameDROPSYNONYMsynonym;

ALTERTABLE[IFEXISTS][ONLY]table_nameDROPSYNONYMsynonym;

The following example shows a DDL request that drops the synonym from the table:

ALTERTABLESingersDROPSYNONYMSingersTest;

ALTERTABLEsingersDROPSYNONYMsingers_test;