ALTER TABLE … COLUMN clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Adds, modifies, or drops a column in a table or a field in a column in a Delta Lake table.

Syntax 

ALTER TABLE table_name
   { ADD COLUMN clause |
     ALTER COLUMN clause |
     DROP COLUMN clause |
     RENAME COLUMN clause }

ADD COLUMN clause

This clause is not supported for JDBC data sources.

Adds one or more columns to the table, or fields to existing columns in a Delta Lake table.

Note

When you add a column to an existing Delta table, you cannot define a DEFAULT value. All columns added to Delta tables are treated as NULL for existing rows. After adding a column, you can optionally define a default value for the column, but this is only applied for new rows inserted into the table. Use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_expression

Syntax 

{ ADD [ COLUMN | COLUMNS ]
  ( { { column_identifier | field_name } data_type
      [ DEFAULT clause ] [ COMMENT comment ] [ FIRST | AFTER identifier ]
      [ MASK clause ] } [, ...] ) }

Parameters

  • column_identifier

    The name of the column to be added. The name must be unique within the table.

    Unless FIRST or AFTER name are specified the column or field will be appended at the end.

  • field_name

    The fully qualified name of the field to be added to an existing column. All components of the path to the nested field must exist and the field name itself must be unique.

  • DEFAULT default_expression

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    Defines a DEFAULT value for the column which is used on INSERT and MERGE ... INSERT when the column is not specified.

    If no default is specified, DEFAULT NULL is implied for nullable columns.

    default_expression may be composed of literals, and built-in SQL functions or operators except:

    default_expression must not contain any subquery.

    DEFAULT is supported for CSV, JSON, PARQUET, and ORC sources.

  • data_type

    Specifies the data type of the column or field. Not all data types supported by Databricks are supported by all data sources.

  • COMMENT comment

    An optional STRING literal describing the added column or field.

    If you want to add an AI-generated comment for a table or table column managed by Unity Catalog, see Add AI-generated comments to Unity Catalog objects.

  • FIRST

    If specified the column will be added as the first column of the table, or the field will be added as the first field of in the containing struct.

  • AFTER identifier

    If specified the column or field will be added immediately after the field or column identifier.

  • MASK clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above check marked yes Unity Catalog only

    Preview

    This feature is in Public Preview.

    Adds a column mask function to anonymize sensitive data. All subsequent queries from that column will receive the result of evaluating that function over the column in place of the column’s original value. This can be useful for fine-grained access control purposes where the function can inspect the identity or group memberships of the invoking user to determine whether to redact the value.

ALTER COLUMN clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Changes a property or the location of a column.

Syntax 

{ { ALTER | CHANGE } [ COLUMN ] { column_identifier | field_name }
  { COMMENT comment |
    { FIRST | AFTER column_identifier } |
    { SET | DROP } NOT NULL |
    TYPE data_type |
    SET DEFAULT clause |
    DROP DEFAULT |
    SYNC IDENTITY |
    SET { MASK clause } |
    DROP MASK |
    SET TAGS clause |
    UNSET TAGS clause } }

Parameters

  • `column_identifier`

    The name of the column to be altered.

  • `field_name`

    The fully qualified name of the field to be altered. All components of the path to the nested field must exist.

  • COMMENT comment

    Changes the description of the column_name column. comment must be a STRING literal.

  • FIRST or AFTER identifier

    Moves the column from its current position to the front (FIRST) or immediately AFTER the identifier. This clause is only supported if table_name is a Delta table.

  • TYPE data_type

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 15.2 and above

    Changes the data type of the column_name column.

    This clause is only supported if table_name is a Delta table.

    The following type changes are supported for all Delta tables:

    • Increasing the size of a VARCHAR column, for example, from VARCHAR(5) to VARCHAR(10)

    • Changing the type of a CHAR column to a VARCHAR, for example, from CHAR(5) to VARCHAR(5)

    • Changing the type of a CHAR or VARCHAR column to STRING, for example, from VARCHAR(10) to STRING.

    The following type changes are supported for Delta tables with delta.enableTypeWidening set to true:

    Preview

    This feature is in Public Preview in Databricks Runtime 15.2 and above.

    Source type

    Supported wider types

    BYTE

    SHORT, INT, BIGINT, DECIMAL, DOUBLE

    SHORT

    INT, BIGINT, DECIMAL, DOUBLE

    INT

    BIGINT, DECIMAL, DOUBLE

    BIGINT

    DECIMAL, DOUBLE

    FLOAT

    DOUBLE

    DECIMAL

    DECIMAL with greater precision and scale

    DATE

    TIMESTAMP_NTZ

    For more detailed information on type widening, see Type widening.

  • SET NOT NULL or DROP NOT NULL

    Changes the domain of valid column values to exclude nulls SET NOT NULL, or include nulls DROP NOT NULL. This option is only supported for Delta Lake tables. Delta Lake will ensure the constraint is valid for all existing and new data.

  • SYNC IDENTITY

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

    Synchronize the metadata of an identity column with the actual data. When you write your own values to an identity column, it might not comply with the metadata. This option evaluates the state and updates the metadata to be consistent with the actual data. After this command, the next automatically assigned identity value will start from start + (n + 1) * step, where n is the smallest value that satisfies start + n * step >= max() (for a positive step).

    This option is only supported for identity columns on Delta Lake tables.

  • DROP DEFAULT

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    Removes the default expression from the column. For nullable columns this is equivalent to SET DEFAULT NULL. For columns defined with NOT NULL you need to provide a value on every future INSERT operation

  • SET DEFAULT default_expression

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    Defines a DEFAULT value for the column which is used on INSERT and MERGE ... INSERT when the column is not specified.

    If no default is specified DEFAULT NULL is implied for nullable columns.

    default_expression may be composed of literals, built-in SQL functions, or operators except:

    default_expression must not contain a subquery.

    DEFAULT is supported for CSV, JSON, ORC, and PARQUET sources.

    When you define the default for a newly added column, the default applies to all pre-existing rows. If the default includes a non-deterministic function such as rand or current_timestamp the value is computed once when the ALTER TABLE is executed, and applied as a constant to pre-existing rows. For newly inserted rows, the default expression runs once per rows.

    When you set a default using ALTER COLUMN, existing rows are not affected by that change.

  • SET MASK clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above check marked yes Unity Catalog only

    Preview

    This feature is in Public Preview.

    Adds a column mask function to anonymize sensitive data. All subsequent queries from that column will receive the result of evaluating that function over the column in place of the column’s original value. This can be useful for fine-grained access control purposes where the function can inspect the identity or group memberships of the invoking user to determine whether to redact the value.

  • DROP MASK

    Applies to: check marked yes Unity Catalog only

    Preview

    This feature is in Public Preview.

    Removes the column mask for this column, if any. Future queries from this column will receive the column’s original values.

  • SET TAGS ( { tag_name = tag_value } [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Apply tags to the column. You need to have APPLY TAG permission to add tags to the column.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

    • tag_value

      A literal STRING.

  • UNSET TAGS ( tag_name [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Remove tags from the column. You need to have APPLY TAG permission to remove tags from the column.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

DROP COLUMN clause

Preview

This feature is in Public Preview.

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Drop one or more columns or fields in a Delta Lake table.

When you drop a column or field, you must drop dependent check constraints and generated columns.

For requirements, see Rename and drop columns with Delta Lake column mapping.

Syntax 

DROP [COLUMN | COLUMNS] [ IF EXISTS ] ( { {column_identifier | field_name} [, ...] )

Parameters

  • IF EXISTS

    When you specify IF EXISTS, Databricks ignores an attempt to drop columns that do not exist. Otherwise, dropping non-existing columns will cause an error.

  • column_identifier

    The name of the existing column.

  • field_name

    The fully qualified name of an existing field.

RENAME COLUMN clause

Preview

This feature is in Public Preview.

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Renames a column or field in a Delta Lake table.

When you rename a column or field you also need to change dependent check constraints and generated columns. Any primary keys and foreign keys using the column will be dropped. In case of foreign keys you must own the table on which the foreign key is defined.

For requirements, see Rename and drop columns with Delta Lake column mapping.

Syntax 

RENAME COLUMN { column_identifier TO to_column_identifier|
                field_name TO to_field_identifier }

Parameters

Examples

See ALTER TABLE examples.