ALTER TABLE¶

Modifies the properties, columns, or constraints for an existing table from the current/specified schema.

See the Snowflake documentation and Iceberg documentation for more details.

Currently, BodoSQL only supports the following operations:

Renaming a table¶

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

Adding columns¶

ALTER TABLE [ IF EXISTS ] <name> 
    ADD [ COLUMN ] [ IF NOT EXISTS ] <new_column_name> <column_datatype>

Warning

This operation is currently only supported for Iceberg.

Only a subset of Iceberg types are supported for ADD COLUMN. The syntax corresponding to the Iceberg types are as follows:

Syntax	Iceberg Type
DECIMAL, NUMERIC	decimal(38, 0)
NUMBER(P, S), DECIMAL(P, S)	decimal(p, s)
INT, INTEGER, SMALLINT, TINYINT, BYTEINT	int
BIGINT	long
FLOAT, FLOAT4, FLOAT8	float
DOUBLE, DOUBLE PRECISION, REAL	double
VARCHAR, CHAR, CHARACTER, STRING, TEXT, BINARY, VARBINARY	string
BOOLEAN	boolean
DATE	date
TIME	time
DATETIME, TIMESTAMP, TIMESTAMP_NTZ	timestamp

Note that adding nested types such as struct<x: double, y: double> is not supported yet. As such, column names including periods are disallowed in order to prevent ambiguity.

Dropping columns¶

ALTER TABLE [ IF EXISTS ] <name> 
    DROP [ COLUMN ] [ IF EXISTS ] <column_name> [ , <column_name>, ...]

Note

This operation is currently only supported for Iceberg.

DROP COLUMN can be used to drop nested columns and fields of structs.

To do so, use . separated field names:

-- Example
ALTER TABLE tblname DROP COLUMN colname.fieldname

Multiple nested columns are also supported:

-- Example
ALTER TABLE tblname DROP COLUMN colname.structname.fieldname

Renaming columns¶

ALTER TABLE [ IF EXISTS ] <name> 
    RENAME COLUMN <column_name> TO <new_column_name>

Note

This operation is currently only supported for Iceberg.
Nested columns can also be renamed. For example,
```
ALTER TABLE table1 RENAME COLUMN column1.field1 TO field2
```
will rename the nested field field1 within the column1 struct to field2.

Altering columns¶

ALTER TABLE [ IF EXISTS ] <name>
    ALTER [ COLUMN ] <column_name> alterColumnAction

Currently, the following options for alterColumnAction are supported:

Setting column comments¶

ALTER TABLE [ IF EXISTS ] <name>
    ALTER [ COLUMN ] <column_name> COMMENT 'comment_string'

Changing nullability of columns¶

ALTER TABLE [ IF EXISTS ] <name>
    ALTER [ COLUMN ] <column_name> DROP NOT NULL

This will change a required column (a column that cannot hold NULL values) to an optional column.

Note

All ALTER COLUMN operations are currently only supported for Iceberg.

Setting / unsetting table properties¶

ALTER TABLE SET is used to set table-wide properties. If a particular property was already set, this overrides the old value with the new one. ALTER TABLE UNSET is used to drop table properties.

Note

This operation is currently only supported for Iceberg.

ALTER TABLE [ IF EXISTS ] <name> 
    SET ( PROPERTY | PROPERTIES | TAG | TAGS | TBLPROPERTY | TBLPROPERTIES ) 
    '<tag_name>' = '<tag_value>' [ , '<tag_name>' = '<tag_value>' ... ]

ALTER TABLE [ IF EXISTS ] <name> 
    UNSET ( PROPERTY | PROPERTIES | TAG | TAGS | TBLPROPERTY | TBLPROPERTIES ) 
    [ IF EXISTS ] '<tag_name>'[ , '<tag_name>' ... ]

Setting / unsetting table comments¶

This operation functions as an alias for ALTER TABLE SET PROPERTY COMMENT='comment'.

Note

This operation is currently only supported for Iceberg.

ALTER TABLE [ IF EXISTS ] <name> SET COMMENT '<comment>'

ALTER TABLE [ IF EXISTS ] <name> UNSET COMMENT