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

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

Expand

Show lessSee more

Ref: 1425