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

ParameterDescription
<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 EXISTSIf specified, this clause prevents an error message that would occur if the column already exists in the table.

​
Column constraints & default expression

ConstraintDescription
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 NULLDetermines if the column may or may not contain NULLs.
UNIQUEProvides 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

ParameterDescription
<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.
FIRSTKeyword indicating that <column_name> will become the first column in the table.
AFTERKeyword 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 SET

Updates table-level parameters for an existing managed table.

​
Syntax

ALTER TABLE <table> SET (<param1>=<value1>[, <param2>=<value2> ...])

​
Parameters

ParameterDescription
<table>Name of the managed table to update.
COMPRESSIONCompression algorithm for new data: ZSTD, LZ4, or DEFAULT.
COMPRESSION_LEVELInteger compression level; applies only when COMPRESSION is specified.
DESCRIPTIONDescription text for the table.

​
Notes

  • This statement applies to managed tables; external tables are not supported.
  • Changing COMPRESSION affects data written after the change; existing data isn’t retroactively recompressed.
  • COMPRESSION_LEVEL is accepted only if COMPRESSION is also set.
  • Required privileges: you must be the table owner or have the permission to modify the table.

​
Examples

Set compression to LZ4: 
ALTER TABLE compression_table SET (COMPRESSION=lz4);
Set compression to ZSTD with level 3: 
ALTER TABLE compression_table SET (COMPRESSION=zstd, COMPRESSION_LEVEL=3);
Reset to default compression and set a description: 
ALTER TABLE compression_table SET (COMPRESSION=DEFAULT, DESCRIPTION='a table with default compression');

​
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

ParameterDescription
<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

ParameterDescription
<table>Name of the table to change the owner of.
<user>The new owner of the table.

​
ALTER TABLE RENAME TO

Renames a table.

​
Syntax

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

​
Parameters

ParameterDescription
<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