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