Snowflake Seems to Auto-Cast VARCHAR to NUMERIC for Insert into VARCHAR Field

user_17493616 0
Viewed 277

When trying to insert data into a Snowflake table, it appears that the presumed type of improperly cast input values may override the data type supplied by the table creation command. For example, given a table created as follows:

CREATE TABLE vctable (
    itemA VARCHAR(8),
    itemB VARCHAR(8)
);

The following command fails:

INSERT INTO vctable (itemA, itemB) VALUES ('hi','ho'), (1,2);
ERROR: Numeric value 'hi' is not recognized

I expect this insert to fail because the second record's data types do not match the table definition (as expected, if I cast the second record's elements as strings, the insert is successful). But the error says that the failure is because Snowflake cannot convert 'hi', which is in the first (correctly formatted) record, to a numeric value. It's as if, having surveyed the inputs, Snowflake has decided to override the data types declared in the table. Why is Snowflake trying to convert the strings away from the table definition?

Incidentally, if the insert is broken into two separate statements, both work.

snowflake-cloud-data-platform
2 Answers

Order of execution.

INSERT INTO vctable (itemA, itemB) VALUES ('hi','ho'), (1,2);

is kind of equivalent to

INSERT INTO vctable
select 'hi','ho'
union all select 1, 2;

which has the subquery:

select 'hi','ho'
union select 1, 2

which fails with Numeric value 'hi' is not recognized.

Then you'll note that even this fails:

select $1::varchar, $2::varchar
from VALUES ('hi','ho'), (1, 2);

It's too late even for a cast at the select level, because values() needs to run first.

user_1324380

You must convert numeric values to string at the VALUES clause level, try this one:

INSERT INTO vctable (itemA, itemB) VALUES ('hi','ho'), (1::string,2::string);
user_126071260
Related