CODEWITHSUNDEEP
databasehashsnowflake-cloud-data-platformalter
atema
atema

Reputation: 21

How to alter virtual column in snowflake

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

Upvotes: 2

Views: 1775

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

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.

Upvotes: -1

SANJIT KHASNOBIS
SANJIT KHASNOBIS

Reputation: 41

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;

Upvotes: 2

Related Questions