CODEWITHSUNDEEP
snowflake-cloud-data-platform
Lowell Boone
Lowell Boone

Reputation: 57

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

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.

Upvotes: 2

Views: 1320

Answers (2)

Michael Golos
Michael Golos

Reputation: 2079

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);

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59375

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.

Upvotes: 2

Related Questions