Snowflake Number out of representable range

user_4043269 0
Viewed 7215

I am trying to insert a number value e.g: -20833.33 in a column data type number(10,6). There is an error in snowflake regarding "number out of representable range". I tried to find an answer why this is happening in numeric data types, but I could not find any plausible explanation.

Here is the representation of the case:

create table test (
   VALUE    number(10, 6)
);


INSERT test 
with TESTTABLE as (
    select '-20833.33' AS VALUE)
SELECT * FROM TESTTABLE

I am receiving the following error: "Number out of representable range: type FIXEDSB8{nullable},value -20833.33

Would someone know why this is happening and have some references that explains this error?

snowflake-cloud-data-platform
1 Answers

You need to increase the precision to NUMBER(11,6) so it can store the 5 digits before of the decimal point, and 6 digits after the decimal point. 11 is the total digits:

create or replace table test (
   VALUE    number(11, 6)
);

INSERT INTO test 
with TESTTABLE as (
    select '-20833.33' AS VALUE)
SELECT * FROM TESTTABLE;

https://docs.snowflake.com/en/sql-reference/data-types-numeric.html#number

user_125509650
Related