Numeric data types¶

NUMBER¶

Numbers up to 38 digits, with an optional precision and scale:

Precision:

Total number of digits allowed.

Scale:

Number of digits allowed to the right of the decimal point.

By default, precision is 38, and scale is 0 (that is, NUMBER(38, 0)). Precision limits the range of values that can be inserted into (or cast to) columns of a given type. For example, the value 999 fits into NUMBER(38,0) but not into NUMBER(2,0).

Because precision is the total number of digits allowed, you can’t load a value into a NUMBER column if the number of digits to the left of the decimal point exceeds the precision of the column minus its scale. For example, NUMBER(20, 2) allows 18 digits on the left side of the decimal point and two digits on the right side of the decimal point, for a total of 20.

The maximum scale (number of digits to the right of the decimal point) is 37. Numbers that have fewer than 38 significant digits, but whose least significant digit is past the 37th decimal place, for example 0.0000000000000000000000000000000000000012 (1.2e-39), can’t be represented without losing some digits of precision.

Snowflake also supports the FLOAT data type, which allows a wider range of values, although with less precision.

DECIMAL , DEC , NUMERIC¶

Synonymous with NUMBER.

INT , INTEGER , BIGINT , SMALLINT , TINYINT , BYTEINT¶

Synonymous with NUMBER, except that precision and scale can’t be specified (that is, it always defaults to NUMBER(38, 0)). Therefore, for all INTEGER data types, the range of values is all integer values from -99999999999999999999999999999999999999 to +99999999999999999999999999999999999999 (inclusive).

The various names (TINYINT, BYTEINT, and so on) are to simplify porting from other systems and to suggest the expected range of values for a column of the specified type.

Impact of precision and scale on storage size¶

Precision (total number of digits) doesn’t affect storage. The storage requirements for the same number in columns with different precisions, such as NUMBER(2,0) and NUMBER(38,0), are the same. For each micro-partition, Snowflake determines the minimum and maximum values for a given column and uses that information to determine the storage size for all values for that column in the partition. For example:

• If a column contains only values between -128 and +127, then each of the values consumes 1 byte (uncompressed).

• If the largest value in the column is 10000000, then each of the values consumes 4 bytes (uncompressed).

However, scale (the number of digits following the decimal point) affects storage. For example, the same value stored in a column of type NUMBER(10,5) consumes more space than NUMBER(5,0). Also, processing values with a larger scale might be slightly slower and consume more memory.

To save space, Snowflake compresses values before writing them to storage. The amount of compression depends on the data values and other factors.

Examples of fixed-point data types in a table¶

The following statement creates a table with columns of various fixed-point data types:

CREATE OR REPLACE TABLE test_fixed(
  num0 NUMBER,
  num10 NUMBER(10,1),
  dec20 DECIMAL(20,2),
  numeric30 NUMERIC(30,3),
  int1 INT,
  int2 INTEGER);

DESC TABLE test_fixed;

+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name      | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| NUM0      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| NUM10     | NUMBER(10,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DEC20     | NUMBER(20,2) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| NUMERIC30 | NUMBER(30,3) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| INT1      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| INT2      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

FLOAT , FLOAT4 , FLOAT8¶

The names FLOAT, FLOAT4, and FLOAT8 are for compatibility with other systems. Snowflake treats all three as 64-bit floating-point numbers.

DOUBLE , DOUBLE PRECISION , REAL¶

Synonymous with FLOAT.

Examples of floating-point data types in a table¶

The following statement creates a table with columns of various floating-point data types:

CREATE OR REPLACE TABLE test_float(
  double1 DOUBLE,
  float1 FLOAT,
  dp1 DOUBLE PRECISION,
  real1 REAL);

DESC TABLE test_float;

+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name    | type  | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| DOUBLE1 | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FLOAT1  | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DP1     | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| REAL1   | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+