How to alter virtual column in snowflake

user_15192274 0
Viewed 858

there is a table with virtual column in snowflake like:

create table my_tbl(
  key       number, 
  name      varchar(60), 
  owner     varchar(60),
  value     number, 
  check_sum number as hash(key, name, value)
);

Could you please advise whether this column is modified with alter statement?

I tried smth like

alter table my_tbl modify column check_sum number as hash(key, name, value, owner)

but it doesn't work

snowflake-cloud-data-platform alter hash database
2 Answers

A quick workaround of the problem I can think of:

create table my_tbl(
  key       number, 
  name      varchar(60), 
  owner     varchar(60),
  value     number, 
  check_sum number as hash(key, name, value)
);

alter table my_tbl add column check_sum_new number as hash(key, name, value, owner);

alter table my_tbl drop column check_sum;

alter table my_tbl rename column check_sum_new to check_sum;

describe table my_tbl;
user_151989320

Fixing my previous answer, as Sanjit notes - you can have a virtual columns in Snowflake.

Although it's not expressly called out in the docs, you can define a virtual column like this:

create temp table my_derived (x int, my_virtual int as (x*10) );

insert into my_derived(x) values(3);

select * from my_derived;

update my_derived set x=40;

select * from my_derived;

(works as expected)

See Sanjit's answer for a solution dropping and adding a new virtual column.

user_1324380
Related