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.

Have you tried this?: stackoverflow.com/a/65026919/38368

Danny Varod
– Danny Varod

2022-12-13 13:01:15 +00:00
Commented Dec 13, 2022 at 13:01 — Danny Varod
– Danny Varod, Commented Dec 13, 2022 at 13:01

Felipe Hoffa · Accepted Answer · 2021-03-12 20:15:00Z

2

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.

answered Mar 12, 2021 at 20:15

Felipe Hoffa

59.8k23 gold badges185 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Deepak Sonawane Over a year ago

Right Felipe. But I was looking for more generic solution. Because if I know for sure that my data will be float always or any other datatype for that matter, I can do it. But in the production workloads if I have to reuse the code for all the datatypes and diverse data, it has to work seamleassly and one not to have to infer datatype for the first record. I guess its a limitation of Snowflake at the moment.

Felipe Hoffa Over a year ago

I'm assuming in production you don't need to run statements like insert into table1(col1) values (-3.4E20); -- and if you do, can you include more about the specific use case so we can harden it for production?

Deepak Sonawane Over a year ago

thats right Felipe, that was one of kind use case that time, we now have reverted back to alternative which is more robust. Thanks for your reply.

Marcel · Accepted Answer · 2021-03-12 10:25:33Z

1

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

answered Mar 12, 2021 at 10:25

Marcel

2,7001 gold badge8 silver badges13 bronze badges

1 Comment

Deepak Sonawane Over a year ago

Thanks for the reply Marcel. I had gone through the documentation. But it didnt helped me clarify my doubt because you see I am getting error at first value itself. And both the values are of the same type and also are valid values as per the datatype.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related