The RENAME COLUMN
statement changes the name of a column in a table.
The RENAME COLUMN
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
It is not possible to rename a column referenced by a view. For more details, see View Dependencies.
This command can be combined with other ALTER TABLE
commands in a single statement. For a list of commands that can be combined, see ALTER TABLE
. For a demonstration, see Add and rename columns atomically.
Synopsis
Required privileges
The user must have the CREATE
privilege on the table.
Parameters
Parameter | Description |
---|---|
IF EXISTS |
Rename the column only if a table of table_name exists; if one does not exist, do not return an error. |
table_name |
The name of the table with the column you want to use. |
current_name |
The current name of the column. |
name |
The name you want to use for the column, which must be unique to its table and follow these identifier rules. |
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS
.
Examples
Rename a column
> CREATE TABLE users (
id INT PRIMARY KEY,
first_name STRING,
family_name STRING
);
> ALTER TABLE users RENAME COLUMN family_name TO last_name;
table_name | create_statement
+------------+--------------------------------------------------+
users | CREATE TABLE users (
| id INT8 NOT NULL,
| first_name STRING NULL,
| last_name STRING NULL,
| CONSTRAINT "primary" PRIMARY KEY (id ASC),
| FAMILY "primary" (id, first_name, last_name)
| )
(1 row)
Add and rename columns atomically
Some subcommands can be used in combination in a single ALTER TABLE
statement. For example, let's say you create a users
table with 2 columns, an id
column for the primary key and a name
column for each user's last name:
> CREATE TABLE users (
id INT PRIMARY KEY,
name STRING
);
Then you decide you want distinct columns for each user's first name, last name, and full name, so you execute a single ALTER TABLE
statement renaming name
to last_name
, adding first_name
, and adding a computed column called name
that concatenates first_name
and last_name
:
> ALTER TABLE users
RENAME COLUMN name TO last_name,
ADD COLUMN first_name STRING,
ADD COLUMN name STRING
AS (CONCAT(first_name, ' ', last_name)) STORED;
> SHOW CREATE TABLE users;
table_name | create_statement
+------------+----------------------------------------------------------------------+
users | CREATE TABLE users (
| id INT8 NOT NULL,
| last_name STRING NULL,
| first_name STRING NULL,
| name STRING NULL AS (concat(first_name, ' ', last_name)) STORED,
| CONSTRAINT "primary" PRIMARY KEY (id ASC),
| FAMILY "primary" (id, last_name, first_name, name)
| )
(1 row)