Working with the Number data type in Snowflake

Introduction 

A small blogpost about a confusion that we had during importing data from Oracle into Snowflake. First the precision is about all of the digits and not the digits before the dot. Next we saw that the values are cut off when they don't fit in the specification of digit and scale.

The examples

CREATE OR REPLACE TABLE numbertest(numberfield number (2,1));

INSERT INTO numbertest(numberfield) SELECT 1;      --Works
INSERT INTO numbertest(numberfield) SELECT 10;     --Doesn't work
INSERT INTO numbertest(numberfield) SELECT 1.1;    --Works
INSERT INTO numbertest(numberfield) SELECT 1.11;   --Works but a Truncation of the precision

SELECT * FROM numbertest

Example 1

In this example we insert a value of 1 into a Number (2,1) and this succeeds.

Example 2

In this example we insert a value of 10 into a Number (2,1) and this returns an error, The precision is 2 and the scale is 1. But the precision is the number of digits in total.

Example 3

This example works fine, one digit before the. and one after the . A total of 2 digits and this is a valid insertion.

Example 4

This example is a bit remarkable because the insertion works fine but the scale (after the .) is cut off and there is no error. The information is not presented correctly.

Example 5

And if we enlarge the scale again (and the precision) all the values are shown correctly again.

Final thoughts

I wasn't aware that his could potentially happen and I'm not sure if this happens also in SQL Server with Floats, real, etc

Hennie