RENAME COLUMN

The RENAME COLUMN statement changes the name of a column in a table.

Note:

It is not possible to rename a column referenced by a view. For more details, see View Dependencies.

Tip:

New in v19.1: 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

ALTERTABLEIFEXISTStable_nameRENAMECOLUMNcurrent_nameTOname

Required privileges

The user must have the CREATE privilege on the table.

Parameters

ParameterDescription
IF EXISTSRename the column only if a column of current_name exists; if one does not exist, do not return an error.
table_nameThe name of the table with the column you want to use.
current_nameThe current name of the column.
nameThe 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

  1. > CREATE TABLE users (
  2.     id INT PRIMARY KEY,
  3.     first_name STRING,
  4.     family_name STRING
  5.   );
  1. > ALTER TABLE users RENAME COLUMN family_name TO last_name;
  1.   table_name |                 create_statement
  2. +------------+--------------------------------------------------+
  3.   users      | CREATE TABLE users (
  4.              |     id INT8 NOT NULL,
  5.              |     first_name STRING NULL,
  6.              |     last_name STRING NULL,
  7.              |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
  8.              |     FAMILY "primary" (id, first_name, last_name)
  9.              | )
  10. (1 row)

Add and rename columns atomically

New in v19.1: 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:

  1. > CREATE TABLE users (
  2.     id INT PRIMARY KEY,
  3.     name STRING
  4.   );

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:

  1. > ALTER TABLE users
  2.     RENAME COLUMN name TO last_name,
  3.     ADD COLUMN first_name STRING,
  4.     ADD COLUMN name STRING
  5.       AS (CONCAT(first_name, ' ', last_name)) STORED;
  1. > SHOW CREATE TABLE users;
  1.   table_name |                           create_statement
  2. +------------+----------------------------------------------------------------------+
  3.   users      | CREATE TABLE users (
  4.              |     id INT8 NOT NULL,
  5.              |     last_name STRING NULL,
  6.              |     first_name STRING NULL,
  7.              |     name STRING NULL AS (concat(first_name, ' ', last_name)) STORED,
  8.              |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
  9.              |     FAMILY "primary" (id, last_name, first_name, name)
  10.              | )
  11. (1 row)

See also

Was this page helpful?
YesNo