CODEWITHSUNDEEP
sql-insertdefault-valuesnowflake-cloud-data-platform
Tyro
Tyro

Reputation: 139

invalid identifier on insert

create table query_results.temporary_20200422(
  a_string varchar,
  a_hash varchar default sha2(a_string,512)
)

is successful. However,

insert into query_results.temporary_20200422(a_string) values('1')

yields

SQL compilation error: error line 1 at position 5 invalid identifier 'TEMPORARY_20200422.A_STRING'

What's going on?

I mean, it's entirely possible that using a function of an existing column as a default value for another column doesn't work — indeed, that's what I was testing — but I'm not convinced that's the problem because the error is at line 1, position 5.

Upvotes: 1

Views: 323

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10079

As you already confirmed, it is not possible to reference to "any columns of the table".

Please check:

https://support.snowflake.net/s/question/0D50Z00009T2MHSSA3/persistent-virtual-column-using-default-on-an-existing-table

Why does it show the error is at line 1, position 5? I think it's about your default expression:

sha2(a_string,512)

The location of a_string is on line 1, position 5 (as we start counting from 0). If you create the table like this, you will see the error is on line 1, position 12:

create or replace table temporary_20200422(
  a_string varchar,
  upstr varchar default lower(upper(a_string))
)
;

insert into temporary_20200422 (a_string) values('1');

SQL compilation error: error line 1 at position 12 invalid identifier 'TEMPORARY_20200422.A_STRING'

Upvotes: 1

Related Questions