Snowflake float type column out of range error

user_7638982 0
Viewed 216

I got Numeric value out of range error when trying to insert two values into a Float type column.

create or replace table num_test(float_num float); -- create table and column
INSERT INTO num_test (float_num)VALUES (1.0528618730874378E10), (-3.694822225952521E-13);

The error i got shows: "Numeric value '10528618730.874378' is out of range."

But when i try to insert these two values separately, it work fine.

INSERT INTO num_test (float_num)VALUES (1.0528618730874378E10); -- ok
INSERT INTO num_test (float_num)VALUES (-3.694822225952521E-13); -- ok

I couldn't see any out of range issues on the snowflake document for the values I tried to insert.

snowflake-cloud-data-platform
1 Answers

If I had to guess what the problem was, it would be that "the guess of the "type" based on the first value in the VALUES is smaller than the type of the second value".

So if we just try and select those values with zero target problems:

select column1, system$typeof(column1) 
from VALUES
    (1.0528618730874378E10),
    (-3.694822225952521E-13);

triggers.

Numeric value '10528618730.874378' is out of range

One at a time we get:

COLUMN1 SYSTEM$TYPEOF(COLUMN1)
-0.0000000000003695 NUMBER(29,28)[SB16]
COLUMN1 SYSTEM$TYPEOF(COLUMN1)
10,528,618,730.874378 NUMBER(17,6)[SB8]

So sure enough the two random "numbers" are cast to two different types, and these are deemed "too different". Thus my guess was correct.

What to do about this:

So we inline cast them:

select column1, system$typeof(column1) 
from VALUES
    (1.0528618730874378E10::double),
    (-3.694822225952521E-13::double)
    ;

we get

COLUMN1 SYSTEM$TYPEOF(COLUMN1)
10,528,618,730.8744 FLOAT[DOUBLE]
-0.0000000000003695 FLOAT[DOUBLE]

so the values are safe if we first tell the DB what they are, and avoid the auto guessing code.

thus in your context:

INSERT INTO num_test (float_num)VALUES
    (1.0528618730874378E10::double),
    (-3.694822225952521E-13::double)
    ;
number of rows inserted
2
user_439920
Related