Renaming columns and tables

Question

Does the spanner supports renaming of columns and tables? That would be helpful while managing schema changes along with creating a new table from an existing one in a single statement.

Frankpledge

Answer 1

You cannot rename a column or a table. Other kinds of alterations are possible, but not renaming. A work-around for renaming columns is available in this answer: Is it possible to rename columns?

Reproductive

Answer 2

I think the accepted answer from Mike Curtiss is out of date.

I just tried the following in Google Cloud console and it worked:

ALTER TABLE Foo RENAME TO Bar

Potentilla

Answer 3

Google Documentation shows that the ALTER TABLE only supports renaming the table, not columns.

https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#alter_table.

To rename columns in Google Spanner usually I follow these steps:

1. Add a new nullable column with the name I want.

ALTER TABLE TableName ADD COLUMN NEW_column_name [DataType]

2. then copy the values from the old column to the new:

 UPDATE TableName Set NEW_column_name = OLD_column_name = WHERE True

3. Drop the old column:

ALTER TABLE TableName DROP COLUMN OLD_column_name

The steps above are the happy path, things can get a little bit more complicated in the column that is being renamed includes indexes, keys, interleave columns, etc. which in some cases will make the add/copy/drop impossible.

Note: Another option is to create a new table with the desired columns names and copy data from the old to the new.

Corneliuscornell