Snowflake : Facing issues while adding multiple values through insert into values

Question

I am trying to add multiple rows in the table in snowflake using "insert into values" statement.

Here is create table statement :

create table table1(col1 float);

I am inserting multiple rows using following command :

insert into table1(col1) values (-3.4E20),(3.4E-20);

I am getting error like

"Numeric value '-340000000000000000000' is not recognized"

On the other hand if I try inserting both the rows separately its getting successful.

Insertion commands :

insert into table1(col1) values (-3.4E20);

insert into table1(col1) values (3.4E-20);

Can you please help me identify the issue with the insert command with multiple rows?

Any suggestions, help will be very helpful.

Answer 1

Expanding on Marcel's answer, this query produces the same error:

select $1
from values (-3.4E20), (3.4E-20);

And this one fixes it:

select $1
from values (-3.4E20::float), (3.4E-20);

As seen in the query, the solution is to add information to the literal number so Snowflake doesn't get confused about possible types.

Answer 2

I'm not sure whether this is related to your problem but maybe it helps finding an answer.

According to docs you have to make sure that the data types of the inserted values are consistent across the rows because the server looks at the data type of the first row as a guide. So... if the datatypes from your first and second values-clause are different (maybe due to some automatically conversion of one of the values above), the combined query will fail. Even if they match the datatype of the table's column!

Link: https://docs.snowflake.com/en/sql-reference/sql/insert.html#multi-row-insert-using-explicitly-specified-values