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> | An identifier that specifies the name of the column that will be added 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 NULLs. |
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 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;
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 <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.
Syntax
ALTER TABLE [IF EXISTS] <table_name> RENAME TO <new_table_name>
Parameters
| Parameter | Description |
|---|
<table_name> | The name of the table to rename. |
<new_table_name> | The new name of the table. |
Limitations
The query can only be executed under the following conditions:
- Only for managed tables created on Firebolt version 4.10 or higher (external tables are not supported).
- The table must not have any dependent views.
- Renaming tables across schemas and databases is not supported. Consider using CREATE TABLE CLONE
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);
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 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
