Skip to main content

ALTER TABLE

Use the ALTER TABLE command to modify the structure of an existing regular table by adding or deleting its columns.

note

Currently, ALTER TABLE does not support tables with connector settings (i.e. materialized sources created with CREATE TABLE). This functionality will be available in future releases.

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 clauseDescription
ADD [ COLUMN ]COLUMN is optional.
column_nameSpecify the name of the column you want to add.
data_typeThe 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 clauseDescription
DROP [ COLUMN ]COLUMN is optional.
column_nameSpecify the column you want to remove.
IF EXISTSDo 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;

Help us make this doc better!