25 October 2018        Add to Favorites   Report

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.


Read great educational content like this and a lot more ! Create my free account now 🎁

Copied