ALTER TABLE: Incompatible Default Values No Longer Allowed in New Columns

Attention

This behavior change is in the 2023_08 bundle.

For the current status of the bundle, refer to Bundle History.

The ALTER TABLE command behaves as follows:

Before the change:

ALTER TABLE ADD COLUMN DEFAULT commands allowed adding a column with a default value that was incompatible with the data type of the column. The resulting default value was unusable. For example, although the default value of 1 is incompatible with the TIMESTAMP_TZ data type, the following command would succeed:

ALTER TABLE t ADD COLUMN x TIMESTAMP_TZ DEFAULT 1;

After the change:

ALTER TABLE ADD COLUMN DEFAULT commands no longer allow adding a column with a default value that is incompatible with the data type of the column. An attempt to set an incompatible default value for a column fails with an error. For example:

SQL compilation error: Expression type does not match column data type, expecting DATE but got NUMBER(1,0) for column Y

The following specific combinations fail:

Data type of column

Data type of DEFAULT value

VARCHAR

BOOLEAN

DATE

BOOLEAN

TIME

BOOLEAN

TIMESTAMP_LTZ

BOOLEAN

TIMESTAMP_NTZ

BOOLEAN

TIMESTAMP_TZ

BOOLEAN

FLOAT

BOOLEAN

NUMBER

BOOLEAN

BOOLEAN

VARCHAR

DATE

FLOAT

TIME

FLOAT

TIMESTAMP_LTZ

FLOAT

TIMESTAMP_NTZ

FLOAT

TIMESTAMP_TZ

FLOAT

DATE

NUMBER

TIME

NUMBER

TIMESTAMP_LTZ

NUMBER

TIMESTAMP_NTZ

NUMBER

TIMESTAMP_TZ

NUMBER

Ref: 1425