Changing the column datatype and length in Snowflake using ALTER TABLE
You can use ALTER TABLE to change the column datatype or drop a column or change its length. The below table shows the allowed operations on a Snowflake column
- Change column data type to a synonymous type (e.g. STRING to VARCHAR)
- Increase the length of a text column (e.g. VARCHAR(50) to VARCHAR(100))
- Drop the default for a column (i.e. DROP DEFAULT)
- Change the nullability of a column (i.e. SET NOT NULL or DROP NOT NULL)
- Change the comments for a column
- Change column data type to a different type (e.g. STRING to NUMBER)
- Decrease the length of a text column (e.g. VARCHAR(50) to VARCHAR(25))
- Change the length (precision or scale) of a numeric column (e.g. NUMBER(5,0) to NUMBER(10,0))
- Add or change the default for a column
alter table t1 alter c3 set data type varchar(50), column c3 drop default;
ALTER TABLE EMPLOYEE MODIFY COLUMN FIRST_NAME VARCHAR(200);
alter table t1 alter column c1 drop not null;
alter table t1 modify c2 drop default;
alter table t1 alter c4 comment '50 character column';
- ALTER COLUMN and MODIFY COLUMN are synonymous (You can use whichever you want to)
- When setting a column to NOT NULL, if the column contains NULL values, an error is returned and no changes are applied to the column.
- Also, TYPE can be used only to increase the length of the specified text column.
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too