3

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

Muhammad Haseeb
  • 634
  • 5
  • 20
  • 1
    Snowflake docs on [ALTER TABLE](https://docs.snowflake.com/en/sql-reference/sql/alter-table.html) explicitly show `MODIFY COLUMN SET DEFAULT .NEXTVAL`. See [PDF of the page](https://share.getcloudapp.com/OAuQ61XP) for historical confirmation. I will try logging a case with Snowflake Support to understand how this is supposed to work. – Marty C. Jun 26 '21 at 01:25
  • Snowflake Support Case 00232019 ([ID 5003r00001PDwl6AAD](https://community.snowflake.com/s/case/5003r00001PDwl6AAD/unsupported-feature-alter-column-set-default)) logged. – Marty C. Jun 26 '21 at 01:30

1 Answers1

1

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.

Muhammad Haseeb
  • 634
  • 5
  • 20