ALTER TABLE
Use the ALTER TABLE
command to modify the structure of an existing regular table by adding or deleting its columns. Tables defined with connector settings but without a schema registry can be altered.
Syntax
ALTER TABLE table_name alter_option;
alter_option
depends on the operation you want to perform on a table.
Adding a new column
ADD [ COLUMN ] column_name data_type [ PRIMARY KEY ]
note
Columns added by this command cannot be used by any existing materialized views or indexes. You must create new materialized views or indexes to reference it.
Parameter or clause | Description |
---|---|
ADD [ COLUMN ] | COLUMN is optional. |
column_name | Specify the name of the column you want to add. |
data_type | The data type of the new column. |
-- Add a column named "age" to a table named "employees" with a data type of integer
ALTER TABLE employees ADD age int;
Dropping an existing column
DROP [ COLUMN ] [ IF EXISTS ] column_name
note
You cannot drop columns referenced by materialized views or indexes.
Parameter or clause | Description |
---|---|
DROP [ COLUMN ] | COLUMN is optional. |
column_name | Specify the column you want to remove. |
IF EXISTS | Do not return an error if the specified column does not exist. A notice is issued instead. |
-- Remove a column named "fax" from the "employees" table
ALTER TABLE employees DROP fax;