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.

5 thoughts on “Unable to Change Column Type with Collation”

  1. Hi Eric, thanks for this post. We encountered the same issue during trial testing of replication tools, applying a DDL schema change in source SQL Server db, with Snowflake (6.14.0) as target system. Do we know if there are plans to resolve this, or a suitable workaround?

    1. Hi Terry,

      I am extremely sorry for the delay in response. I had been busy and did not work on my blog for a while.

      I see that supporting this in Snowflake might be challenging due to many metadata need to be updated if a collation has changed for a column.

      As mentioned in my blog post, the only workaround I am aware of is to drop, re-create the table and then re-populate the data.

      I hope it can be helpful.

      Thanks
      Eric

      1. Hi Eric, thanks for response. I suppose this issue is not shared by many Snowflake clients so may be a lower priority. Are we (users) able to “up vote” issues we would like Snowflake to address? I know Microsoft adopt a similar approach for SQL Server issues…

        We had to apply a work-around practice, and tell the replication tool to ignore DDL changes (via cdc tracking) and apply DDL changes manually to source and target systems.

        Thanks
        Terry

Leave a Reply

Your email address will not be published.