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

You can,

• 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
  

You Cannot,

• 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
  

Examples:

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';

NOTE:

• 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.
  

DataFreak

posted on 25 Oct 18