ALTER TABLE

Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API

Change the definition of a table.

Syntax

ALTER TABLE [IF EXISTS] <name> RENAME TO <new_name>;
ALTER TABLE [IF EXISTS] <name> RENAME [COLUMN] <column_name> TO <new_name>;

ALTER TABLE [IF EXISTS] <name>
  ADD [COLUMN] [IF NOT EXISTS] <column_name> <data_type>
  [COLLATE <collation>]
  [<column_constraint>] [...];

ALTER TABLE [IF EXISTS] DROP [COLUMN] [IF EXISTS] <column_name>

ALTER TABLE [IF EXISTS] <name> ADD <table_constraint>;

where column_constraint is:

{ NOT NULL | NULL | DEFAULT default_expr }

and table_constraint is as described in CREATE TABLE.

Description

ALTER TABLE changes the definition of an existing table. Hyper currently only supports the following changes:

• RENAME

  • : The RENAME forms change the name of a table, the name of an individual column in a table, or the name of a constraint of the table. There is no effect on the stored data.

• ADD COLUMN [ IF NOT EXISTS ]

  • : This form adds a new column to the table, using the same syntax as CREATE TABLE. If IF NOT EXISTS is specified and a column already exists with this name, no error is thrown. In contrast to CREATE TABLE, which supports any variable-free expression, including non-constant expressions like random(), the default value here must be a constant.

• DROP COLUMN [ IF EXISTS ]

  • : This form drops a column from a table. Columns with indexes and table constraints can't be dropped.

• ADD table_constraint [ NOT VALID ]

  • : This form adds a new constraint to a table using the same syntax as CREATE TABLE.

Some forms of ALTER TABLE that act on a single table can be combined into a list of multiple alterations to be applied together. It is possible to add several columns and/or drop several columns in a single command. It is also possible to create multiple column constraints in a single command. It is not possible to combine altering columns with adding constraints.

Parameters

• IF EXISTS

  • : Do not throw an error if the table does not exist.

• <name>

  • : The name (optionally schema-qualified) of an existing table to alter.

• <column_name>

  • : Name of a new or existing column.

• <new_column_name>

  • : New name for an existing column.

• <new_name>

  • : New name for the table.

• <data_type>

  • : Data type of the new column.

Notes

The key word COLUMN is noise and can be omitted.

Changing any part of a system catalog table is not permitted.

Examples

To add a column of type text to a table:

    ALTER TABLE distributors ADD COLUMN address text;

To drop a column from a table:

    ALTER TABLE distributors DROP COLUMN address;

To rename an existing column:

    ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

    ALTER TABLE distributors RENAME TO suppliers;

To add an unnamed assumed foreign key constraint to a table:

    ALTER TABLE distributors ADD ASSUMED FOREIGN KEY (address) REFERENCES addresses (address);

See CREATE TABLE for more examples of table constraints.