Snowflake alter column to have default value

Question

I have a table in snowflake. I want to alter one column so that it can have the default value.

Following is the structure:

I want to set the default value for LAST_UPDATED column.

I am running this query:

alter table "TEST_STATUS" modify LAST_UPDATED set default CURRENT_TIMESTAMP() ;

I am getting error as:

Unsupported feature 'Alter Column Set Default'.

How do I alter the table?

Answer 1

You can not use ALTER TABLE to change the default for a column unless it's a sequence or add a column default.

Check Default Values section in here

You need to recreate your table

Answer 2

a default value on a table has to behaviors,

1. when null use 42, which can be done implemented as read operation
2. when inserting null use a more complex form to set a value. like seq() or current_date(), this can only be done on write.

The latter form on some data bases just "rewrites" the data then and there, but given Snowflake is a no free lunch/no hidden costs. If you want your table rewritten (to push on a complex new value like the second case) you should rewrite your table. When you have a simple table with 10 rows, this can seem absurd, to make you jump through hoops like this. But when you have tables with terabytes of data, rewriting all that data take a lot of compute time and more importantly is definitely not atomic, thus it needs to be intentionally done as put of a structured data migration process.

Which like nearly everything about Snowflake, it's designed to do heavy lifting, and thus big tasks should be planned tasks. Thus a large rewrite might be a bigger warehouse, and pausing ingress data processes.