ALTER TABLE

Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API

Change the definition of a table.

1ALTER TABLE [IF EXISTS] <name> RENAME TO <new_name>;
2ALTER TABLE [IF EXISTS] <name> RENAME [COLUMN] <column_name> TO <new_name>;
3
4ALTER TABLE [IF EXISTS] <name>
5  ADD [COLUMN] [IF NOT EXISTS] <column_name> <data_type>
6  [COLLATE <collation>]
7  [<column_constraint>] [...];
8
9ALTER TABLE [IF EXISTS] DROP [COLUMN] [IF EXISTS] <column_name>
10
11ALTER TABLE [IF EXISTS] <name> ADD <table_constraint>;

where column_constraint is:

1{ NOT NULL | NULL | DEFAULT default_expr }

and table_constraint is as described in CREATE TABLE.

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.

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.

The key word COLUMN is noise and can be omitted.

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

To add a column of type text to a table:

1ALTER TABLE distributors ADD COLUMN address text;

To drop a column from a table:

1ALTER TABLE distributors DROP COLUMN address;

To rename an existing column:

1ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

1ALTER TABLE distributors RENAME TO suppliers;

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

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

See CREATE TABLE for more examples of table constraints.