# Numeric Data Types¶

This topic describes the numeric data types supported in Snowflake, along with the supported formats for numeric constants/literals.

In this Topic:

## Data Types for Fixed-point Numbers¶

Snowflake supports the following data types for fixed-point numbers.

### 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` (i.e. `NUMBER(38, 0)`). Note that 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)`.

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), cannot be represented without losing some digits of precision.

Note

If data is converted to another data type with lower precision, then back to the higher-precision form, the data can lose precision. For example, you lose precision if you convert a NUMBER(38,37) value to DOUBLE (which has a precision of approximately 17 decimal digits), and then back to NUMBER.

### DECIMAL , NUMERIC¶

Synonymous with NUMBER.

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

Synonymous with NUMBER, except that precision and scale cannot be specified (i.e. always defaults to `NUMBER(38, 0)`).

### Impact of Precision and Scale on Storage Size¶

Precision (total number of digits) does not impact storage. In other words, 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 range to store all values for that column in the partition. For example:

• If a column contains five values (e.g `0`, `1`, `2`, `3`, `4`), each of the values consumes 1 byte (uncompressed; actual storage size is reduced due to compression).

• If a column contains longer values (e.g. `0`, `1`, `2`, `3`, `4`, `10000000`), each of the values consumes 4 bytes (uncompressed).

However, scale (number of digits following the decimal point) does have an impact on 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 could be slightly slower and consume more memory.

### Examples of Fixed-point Data Types in a Table¶

```CREATE OR REPLACE TABLE test_fixed(num NUMBER,
num10 NUMBER(10,1),
dec DECIMAL(20,2),
numeric NUMERIC(30,3),
int INT,
integer INTEGER
);

DESC TABLE test_fixed;

+---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name    | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| NUM     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| NUM10   | NUMBER(10,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| DEC     | NUMBER(20,2) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| NUMERIC | NUMBER(30,3) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| INT     | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| INTEGER | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
```

## Data Types for Floating Point Numbers¶

Snowflake supports the following data types for floating point numbers.

### FLOAT , FLOAT4 , FLOAT8¶

Snowflake uses double-precision (64 bit) IEEE 754 floating point numbers.

Snowflake supports the following special values for FLOAT:

• `'NaN'` (Not A Number).

• `'inf'` (infinity).

• `'-inf'` (negative infinity).

The symbols `'NaN'`, `'inf'`, and `'-inf'` must be in single quotes, and are case-insensitive.

Comparison semantics for `'NaN'` differ from the IEEE 754 standard in the following ways:

Condition

Snowflake

IEEE 754

Comment

`'NaN' = 'NaN'`

`TRUE`

`FALSE`

In Snowflake, `'NaN'` values are all equal.

`'NaN' > X` . where `X` is any FLOAT value, including . infinity (other than `NaN` itself).

`TRUE`

`FALSE`

Snowflake treats `'NaN'` as greater . than any other FLOAT value, . including infinity.

### DOUBLE , DOUBLE PRECISION , REAL¶

Synonymous with FLOAT.

### Examples of Floating Point Data Types in a Table¶

```CREATE OR REPLACE TABLE test_float(d DOUBLE,
f FLOAT,
dp DOUBLE PRECISION,
r REAL
);

DESC TABLE test_float;

+---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name    | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| D       | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| F       | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| DP      | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| R       | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+---------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
```

Note

DOUBLE, FLOAT, DOUBLE PRECISION, and REAL columns are displayed as FLOAT, but stored as DOUBLE. This is a known issue in Snowflake.

## Numeric Constants¶

Constants (also known as literals) refers to fixed data values. The following formats are supported for numeric constants:

`[+-][digits][.digits][e[+-]digits]`

Where:

• `+` or `-` indicates a positive or negative value. The default is positive.

• `digits` is one or more digits from 0 to 9.

• `e` (or `E`) indicates an exponent in scientific notation. At least one digit must follow the exponent marker if present.

The following numbers are all examples of supported numeric constants:

```15
+1.34
0.2
15e-03
1.234E2
1.234E+2
-1
```