Alter column set default unsupported feature

Question

I want to alter the table and set the default sequence of a column which is identity. When I try to run

ALTER TABLE report.test_table MODIFY id set default test_table_seq.NEXTVAL;

it shows following error:

[0A000][2] Unsupported feature 'Alter Column Set Default'.

Here's create table sql:

create table report.test_table(

    id int identity,
    txt text
);

Considering snowflake documentation a column must have a sequence to use alter column set default and trusting snowflake docs too identity or autoincrement are synonyms and snowflake use sequence to autoincrement that column. https://docs.snowflake.net/manuals/sql-reference/sql/create-table.html

Snowflake docs on ALTER TABLE explicitly show MODIFY COLUMN <col1_name> SET DEFAULT <seq_name>.NEXTVAL. See PDF of the page for historical confirmation. I will try logging a case with Snowflake Support to understand how this is supposed to work. — Marty C.
Snowflake Support Case 00232019 (ID 5003r00001PDwl6AAD) logged. — Marty C.

Muhammad Haseeb Muhammad Haseeb · Accepted Answer · 2020-01-29T13:54:24

Sadly, there's no other way. Snowflake uses a sequence in backend but doesn't allow applying another sequence on that. You can only alter the column to add a new sequence if it was added as default while table creation.

Alter column set default unsupported feature

1 Answers