How to edit CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH in Snowflake

Question

I have two tables in Snowflake. And I need to make one similar to the second. There're two characteristics that I need to change because they differ between each other:

• CHARACTER_MAXIMUM_LENGTH from 50 to 100
• CHARACTER_OCTET_LENGTH from 200 to 400

I know how to change more common characteristics like COLUMN_DEFAULT, IS_NULLABLE, etc. I do it by defining the schema for the table, an. example bellow:

COLUMN_NAME VARCHAR(50) NOT NULL DEFAULT 'NotSpecified'

But with those two I tried the same way and it didn't work.

COLUMN_NAME VARCHAR(50) NOT NULL DEFAULT 'NotSpecified' CHARACTER_MAXIMUM_LENGTH(100) CHARACTER_OCTET_LENGTH(400)

And I receive the error:

SQL compilation error: syntax error line 2 at position 47 unexpected 'CHARACTER_MAXIMUM_LENGTH'. syntax error line 2 at position 71 unexpected '('. syntax error line 2 at position 99 unexpected '('. syntax error line 3 at position 12 unexpected 'NUMBER'. syntax error line 3 at position 18 unexpected '('.

How to change such characteristics for some column in Snowflake?

Answer 1

It is not possible in Snowflake to set a minimum column length.

If you specify VARCHAR(50), then CHARACTER_MAXIMUM_LENGTH is showing you the maximum length in characters for that string and CHARACTER_OCTET_LENGTH is showing you the maximum length in bytes for that string.

However, this is always the maximum and you cannot specify a minimum as per my knowledge.