25 Oct 18 · DataFreak · #sql ·   Bookmark   ×

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


alter table t1 alter c3 set data type varchar(50), column c3 drop default;
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.


posted on 25 Oct 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds