ALTER TABLE - Firebolt Documentation

Updates the specified table.

ALTER TABLE ADD COLUMN

Adds a column to an existing table.

Syntax

ALTER TABLE <table> ADD COLUMN [IF NOT EXISTS] <column_name> <column_type> [NULL | NOT NULL] [DEFAULT <expression>] [UNIQUE]

Parameters

Parameter	Description
`<table>`	Name of the table to which to add the column.
`<column_name>`	Name of the column to add to the table.
`<column_type>`	Specifies the data type for the column.
`IF NOT EXISTS`	If specified, this clause prevents an error message that would occur if the column already exists in the table.

Column constraints & default expression

Constraint	Description
`DEFAULT <expression>`	Determines the value that will be used for the column when this column is omitted in an `INSERT` statement. It also determines the value that is used for the rows that were inserted before the column was added.
`NULL` \| `NOT NULL`	Determines if the column may or may not contain `NULL`s.
`UNIQUE`	Provides metadata to the query optimizer about column uniqueness. Does not enforce uniqueness - ensuring unique values is the user’s responsibility.

Limitations

The query can only be executed under the following conditions:

Only on managed tables, external tables are not supported.
The table must not have any dependent views.
The default expression must contain only literals or functions CURRENT_DATE(), LOCALTIMESTAMP(), CURRENT_TIMESTAMP(), and NOW().

ALTER TABLE DROP COLUMN

Removes a column from an existing table.

Syntax

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

Parameters

Parameter	Description
`<table>`	Name of the table from which to drop the column.
`<column_name>`	Name of the column to remove from the table.
`IF EXISTS`	If specified, this clause prevents an error message that would occur if the column does not exist in the table.

Limitations

The query can only be executed under the following conditions:

Only on managed tables, external tables are not supported.
The table must not have any dependent views.
The column must not be part of the table’s primary index or partition key.
The column must not be covered by a multi-column UNIQUE constraint. Drop the constraint first with ALTER TABLE DROP UNIQUE.
The column must not be used by any aggregating index on the table (as a grouping key or within the index’s aggregation expressions). Drop or alter those indexes first. See DROP INDEX.

Examples

Drop a column from a table:

ALTER TABLE my_table DROP COLUMN old_column;

Drop a column only if it exists:

ALTER TABLE my_table DROP COLUMN IF EXISTS optional_column;

ALTER TABLE RENAME COLUMN

Renames a column in an existing table while preserving all data.

Syntax

ALTER TABLE [IF EXISTS] <table_name> RENAME COLUMN [IF EXISTS] <old_column_name> TO <new_column_name>

Parameters

Parameter	Description
`<table_name>`	Name of the table containing the column to rename.
`<old_column_name>`	Current name of the column to rename.
`<new_column_name>`	New name for the column.
`IF EXISTS` (table level)	Prevents error if table doesn’t exist.
`IF EXISTS` (column level)	Prevents error if column doesn’t exist.

Limitations

The query can only be executed under the following conditions:

Only on managed tables, external tables are not supported.
The table must not have any dependent views.
Cannot rename to an existing column name.
Column must exist (unless IF EXISTS specified).

Examples

Rename a column:

ALTER TABLE users RENAME COLUMN name TO full_name;

Rename a column with IF EXISTS:

ALTER TABLE users RENAME COLUMN IF EXISTS middle_name TO mi;

ALTER TABLE MODIFY COLUMN

Changes the ordinal position of a column within a table’s schema.

Syntax

ALTER TABLE <table> MODIFY COLUMN <column_name> [FIRST | AFTER <other_column>]

Parameters

Parameter	Description
`<table>`	Name of the table containing the column to reposition.
`<column_name>`	Name of the column whose position will be changed. This column must exist in the table.
`<other_column>`	Name of the column after which `<column_name>` will be positioned. This column must exist in the table.
`FIRST`	Keyword indicating that `<column_name>` will become the first column in the table.
`AFTER`	Keyword indicating that `<column_name>` will be positioned after `<other_column>`.

Limitations

The query can only be executed under the following conditions:

Only on managed tables, external tables are not supported.
The table must not have any dependent views.

Examples

Move a column to be the first column in the table:

ALTER TABLE my_table MODIFY COLUMN user_id FIRST;

Move a column to appear after another specific column:

ALTER TABLE my_table MODIFY COLUMN email AFTER user_name;

ALTER TABLE DROP PARTITION

Use to delete a partition from a fact or dimension table.

Dropping a partition deletes the partition and the data stored in that partition.

Syntax

ALTER TABLE <table> DROP PARTITION <value1>[,...<value2]

Parameters

Parameter	Description
`<table>`	Name of the table from which to drop the partition.
`<value1>[,...<value2>]`	An ordered set of one or more values corresponding to the partition key definition. This specifies the partition to drop. When dropping partitions with composite keys (more than one key value), specify all key values in the same order as they were defined. Only partitions with values that match the entire composite key are dropped.

Examples

See the examples in Working with partitions.

ALTER TABLE OWNER TO

Change the owner of a table. The current owner of a table can be viewed in the information_schema.tables view on table_owner column. check ownership page for more info.

Syntax

ALTER TABLE [IF EXISTS] <table> OWNER TO <user>

Parameters

Parameter	Description
`<table>`	Name of the table to change the owner of.
`<user>`	The new owner of the table. Can be either a user name or a role name.

ALTER TABLE RENAME TO

Renames a table or a view.

Syntax

ALTER TABLE [IF EXISTS] <table_name> RENAME TO <new_table_name>

Parameters

Parameter	Description
`<table_name>`	The name of the table or view to rename.
`<new_table_name>`	The new name for the table or view.

Examples

Rename a table:

ALTER TABLE orders RENAME TO archived_orders;

Rename a view:

ALTER TABLE my_view RENAME TO renamed_view;

Rename only if the table or view exists:

ALTER TABLE IF EXISTS my_view RENAME TO renamed_view;

Limitations

The query can only be executed under the following conditions:

External tables are not supported.
The table or view must not have any dependent views.
Renaming across schemas and databases is not supported. Consider using CREATE TABLE CLONE for tables.

ALTER TABLE SET PRIMARY INDEX

Changes the primary index columns of an existing table. This operation only updates the table metadata definition without modifying existing tablets. New ingested data will use the updated primary index definition, while existing tablets retain their original structure until a VACUUM (UPGRADE=true) operation is performed, thus affecting query performance.

Syntax

ALTER TABLE <table_name> SET PRIMARY INDEX ( <column_name1>[, <column_name2>, ...] );

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name1, ...>`	The columns to include in the new primary index. All columns must exist in the table.

Examples

Change to a single column primary index:

ALTER TABLE sales_data SET PRIMARY INDEX (customer_id);

Change to a multi-column primary index:

ALTER TABLE sales_data SET PRIMARY INDEX (customer_id, order_date);

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
If your table contains older tablets that don’t have the primary index information in their metadata headers, you must run VACUUM (UPGRADE=true) on the table before altering the primary index.
Tables with Text Indexes cannot have their primary index altered. You must drop all Text Indexes before modifying the primary index.
All specified columns must exist in the table.

The ALTER TABLE operation only updates the table’s metadata definition. Existing data in tablets retains the original primary index structure until VACUUM (UPGRADE=TRUE) is run.

ALTER TABLE DROP PRIMARY INDEX

Removes the primary index from an existing table. This operation only updates the table metadata definition without modifying existing tablets. New ingested data will not use a primary index, while existing tablets retain their original structure until a VACUUM (UPGRADE=true) operation is performed.

Syntax

ALTER TABLE <table_name> DROP PRIMARY INDEX;

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.

Examples

Remove the primary index:

ALTER TABLE sales_data DROP PRIMARY INDEX;

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
If your table contains older tablets that don’t have the primary index information in their metadata headers, you must run VACUUM (UPGRADE=true) on the table before dropping the primary index.
Tables with Text Indexes cannot have their primary index altered. You must drop all Text Indexes before dropping the primary index.

The ALTER TABLE operation only updates the table’s metadata definition. Existing data in tablets retains the original primary index structure until VACUUM (UPGRADE=TRUE) is run.

ALTER TABLE ADD STATISTICS

Add accitional [automated column statistics][acs] to collect for a column.

Syntax

ALTER TABLE <table_name> ADD STATISTICS (<column_name>) [TYPE <statistics_type>] [TYPE ...n];

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name>`	The column to add statistics for.
`<statistics_type>`	Type of [automated column statistics][acs] to collect. Possible values: `NDISTINCT`, `MINMAX`.

Examples

Collect the number of distinct values (NDISTINCT) for the column customer_id:

ALTER TABLE sales_data ADD STATISTICS (customer_id) TYPE NDISTINCT;

Collect all types of statistics for the column customer_id:

ALTER TABLE sales_data ADD STATISTICS (customer_id);

ALTER TABLE ALTER COLUMN SET UNIQUE

Adds a UNIQUE constraint to an existing column to indicate your intent for the column’s values to be unique. Firebolt does not validate existing data or enforce uniqueness for future writes.

Syntax

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET UNIQUE;

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name>`	The name of the column to add the unique constraint to. The column must exist in the table.

Examples

Add a unique constraint to a column:

ALTER TABLE users ALTER COLUMN email SET UNIQUE;

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
The specified column must exist in the table.
Cannot modify inner struct type fields

ALTER TABLE ALTER COLUMN DROP UNIQUE

Removes the UNIQUE constraint from an existing column. This updates the table metadata to remove the uniqueness declaration.

Syntax

ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP UNIQUE;

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name>`	The name of the column to remove the unique constraint from. The column must exist in the table.

Examples

Remove a unique constraint from a column:

ALTER TABLE users ALTER COLUMN email DROP UNIQUE;

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
The specified column must exist in the table.
Cannot modify inner struct type fields

ALTER TABLE ADD UNIQUE

Adds a UNIQUE constraint over one or more columns of an existing table. A multi-column constraint declares that the combination of values in the covered columns is unique across all rows. Firebolt does not validate existing data or enforce uniqueness for future writes; the constraint provides metadata to the query optimizer. See UNIQUE constraints as query hints. A single-column ADD UNIQUE (a) is equivalent to ALTER COLUMN a SET UNIQUE and is stored as a column-level constraint.

Syntax

ALTER TABLE <table_name> ADD UNIQUE (<column_name>[, <column_name>[, ...n]]);

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name>`	A column covered by the constraint. The column must exist in the table and may appear only once.

Examples

Declare the combination of device_id and event_time unique:

ALTER TABLE events ADD UNIQUE (device_id, event_time);

A constraint may cover a STRUCT column, declaring the combination of all the struct’s fields unique:

ALTER TABLE user_profiles ADD UNIQUE (profile);

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
All specified columns must exist in the table, and a column may appear only once per constraint.
An identical constraint must not already exist on the table. Constraints are compared by their column set, so UNIQUE (a, b) and UNIQUE (b, a) are the same constraint.

ALTER TABLE DROP UNIQUE

Removes the UNIQUE constraint over the given columns from an existing table. The constraint is matched by its column set, so the column order does not matter. A single-column DROP UNIQUE (a) is equivalent to ALTER COLUMN a DROP UNIQUE and also removes a constraint declared on the column itself.

Syntax

ALTER TABLE <table_name> DROP UNIQUE (<column_name>[, <column_name>[, ...n]]);

Parameters

Parameter	Description
`<table_name>`	The name of the table to modify.
`<column_name>`	A column covered by the constraint to remove. The column must exist in the table.

Examples

Remove the constraint over device_id and event_time:

ALTER TABLE events DROP UNIQUE (event_time, device_id);

Limitations

The query can only be executed under the following conditions:

Only supported on FACT and DIMENSION tables. External tables are not supported.
All specified columns must exist in the table.
A UNIQUE constraint over exactly the given columns must exist on the table.

​ALTER TABLE ADD COLUMN

​Syntax

​Parameters

​Column constraints & default expression

​Limitations

​ALTER TABLE DROP COLUMN

​Syntax

​Parameters

​Limitations

​Examples

​ALTER TABLE RENAME COLUMN

​Syntax

​Parameters

​Limitations

​Examples

​ALTER TABLE MODIFY COLUMN

​Syntax

​Parameters

​Limitations

​Examples

​ALTER TABLE DROP PARTITION

​Syntax

​Parameters

​Examples

​ALTER TABLE OWNER TO

​Syntax

​Parameters

​ALTER TABLE RENAME TO

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE SET PRIMARY INDEX

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE DROP PRIMARY INDEX

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE ADD STATISTICS

​Syntax

​Parameters

​Examples

​ALTER TABLE ALTER COLUMN SET UNIQUE

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE ALTER COLUMN DROP UNIQUE

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE ADD UNIQUE

​Syntax

​Parameters

​Examples

​Limitations

​ALTER TABLE DROP UNIQUE

​Syntax

​Parameters

​Examples

​Limitations

ALTER TABLE ADD COLUMN

Syntax

Parameters

Column constraints & default expression

Limitations

ALTER TABLE DROP COLUMN

Syntax

Parameters

Limitations

Examples

ALTER TABLE RENAME COLUMN

Syntax

Parameters

Limitations

Examples

ALTER TABLE MODIFY COLUMN

Syntax

Parameters

Limitations

Examples

ALTER TABLE DROP PARTITION

Syntax

Parameters

Examples

ALTER TABLE OWNER TO

Syntax

Parameters

ALTER TABLE RENAME TO

Syntax

Parameters

Examples

Limitations

ALTER TABLE SET PRIMARY INDEX

Syntax

Parameters

Examples

Limitations

ALTER TABLE DROP PRIMARY INDEX

Syntax

Parameters

Examples

Limitations

ALTER TABLE ADD STATISTICS

Syntax

Parameters

Examples

ALTER TABLE ALTER COLUMN SET UNIQUE

Syntax

Parameters

Examples

Limitations

ALTER TABLE ALTER COLUMN DROP UNIQUE

Syntax

Parameters

Examples

Limitations

ALTER TABLE ADD UNIQUE

Syntax

Parameters

Examples

Limitations

ALTER TABLE DROP UNIQUE

Syntax

Parameters

Examples

Limitations