Unable to Change Column Type with Collation

Currently Snowflake does not support the change of COLLATIONs at COLUMN level once table is defined. This is documented on Snowflake’s official documentation as below:

Once a table column is defined, you cannot change the collation for the column. In other words, after a column has been created with a particular collation using a CREATE TABLE statement, you cannot use ALTER TABLE to change the collation.

Additional Considerations for Using Collation

This actually causes an issue that you will NOT be able to change the column data type if COLLATION has been defined on a column, because Snowflake will detect incompatible collations due to no collation defined for the new column type.

To demonstrate this issue, please see below example:

CREATE TABLE test (col varchar(255) COLLATE 'utf8');

ALTER TABLE test ALTER COLUMN col SET DATA TYPE VARCHAR(500) COLLATE 'utf8';

>> SQL compilation error: syntax error line 1 at position 61 unexpected 'COLLATE'.

ALTER TABLE test ALTER COLUMN col SET DATA TYPE VARCHAR(500);

>> SQL compilation error: cannot change column COL from type "VARCHAR(255) COLLATE 'utf8'" to "VARCHAR(500)" because they have incompatible collations.

You can see from above example, I was trying to change a column’s type from varchar(255) to varchar(500), which is common operation when we realize that the current varchar limit is not enough for our growing data. However, because ALTER TABLE … ALTER COLUMN … does not support COLLATE clause, we are unable to define the COLLATION for the new data type. And since we don’t specify the COLLATION, Snowflake will see COLLATIONs are conflicted between the new and old data type. If we do not have COLLATION defined for the table at creation time, then the ALTER COLUMN can be successful. See below:

CREATE OR REPLACE TABLE test (col varchar(255));
ALTER TABLE test ALTER COLUMN col SET DATA TYPE VARCHAR(500);

>> Statement executed successfully.

Currently there is no easy way to workaround the issue, rather than to drop and re-create the table, of course after you have backed up your data, or use the Time Travel feature Snowflake provides.