숫자 데이터 타입¶

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

고정 소수점 숫자의 데이터 타입¶

Snowflake는 고정 소수점 숫자에 대해 다음 데이터 타입을 지원합니다.

NUMBER¶

최대 38자리 숫자로, 다음과 같은 선택적 전체 자릿수와 소수 자릿수를 갖습니다.

전체 자릿수:: 허용되는 총 자릿수입니다.
소수 자릿수:: 소수점 오른쪽에 허용되는 자릿수입니다.

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 digits.

The maximum scale, which is the 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.

참고

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

Snowflake는 FLOAT 데이터 타입도 지원하므로, 전체 자릿수는 줄어도 더 넓은 범위의 값을 허용합니다.

DECIMAL , DEC , NUMERIC¶

NUMBER와 동의어입니다.

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

NUMBER 와 동일하지만 정밀도와 배율을 지정할 수 없다는 점을 예외로 합니다(즉, 항상 NUMBER(38, 0)으로 기본값이 지정됨). 따라서 모든 INTEGER 데이터 타입의 경우 값 범위는 -99999999999999999999999999999999999999에서 +99999999999999999999999999999999999999(포함)까지의 모든 정수 값입니다.

The various names — for example, 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.

전체 자릿수와 소수 자릿수가 저장소 크기에 미치는 영향¶

Precision — the 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, each of the values consumes 1 byte (uncompressed).
If the largest value in the column is 10000000, 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.

공간을 절약하기 위해, Snowflake는 값을 압축한 후에 저장소에 기록합니다. 압축량은 데이터 값과 기타 요인에 따라 다릅니다.

테이블에 있는 고정 소수점 데이터 타입의 예¶

다음 문은 다양한 고정 소수점 데이터 타입의 열이 있는 테이블을 생성합니다.

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;

Copy

+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| 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           |
+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

부동 소수점 숫자의 데이터 타입¶

Snowflake는 부동 소수점 숫자에 대해 다음 데이터 타입을 지원합니다.

FLOAT , FLOAT4 , FLOAT8¶

FLOAT, FLOAT4, FLOAT8 은 다른 시스템과의 호환성을 위한 이름입니다. Snowflake는 이 세 가지를 모두 64비트 부동소수점 숫자로 취급합니다.

전체 자릿수¶

Snowflake는 배정밀도(64비트) IEEE 754 부동 소수점 숫자를 사용합니다.

Precision is approximately 15 digits. For example, for integers, the range is from -9007199254740991 to +9007199254740991 (-2⁵³ + 1 to +2⁵³ - 1). Floating-point values can range from approximately 10^-308 to 10⁺³⁰⁸. Snowflake can represent more extreme values between approximately 10^-324 and 10^-308 with less precision. For more details, see the Wikipedia article on double-precision numbers.

Snowflake는 고정 소수점 데이터 타입 NUMBER 를 지원하므로, 지수 범위가 더 작아도 더 큰 전체 자릿수를 허용합니다.

특수한 값¶

Snowflake는 FLOAT에 대해 다음과 같은 특수한 값을 지원합니다.

'NaN' (숫자가 아님).
'inf' (무한대).
'-inf' (음의 무한대).

'NaN', 'inf', '-inf' 기호는 작은따옴표로 묶어야 하며 대/소문자를 구분하지 않습니다.

'NaN' 에 대한 비교 의미 체계는 다음과 같은 식으로 IEEE 754 표준과는 다릅니다.

조건	Snowflake	IEEE 754	설명
`'NaN' = 'NaN'`	`TRUE`	`FALSE`	Snowflake에서는 `'NaN'` 값이 모두 같습니다.
`'NaN' > X` . where `X` is any FLOAT value, including . infinity, other than `NaN` itself.	`TRUE`	`FALSE`	Snowflake에서 `'NaN'` 은 무한대를 포함한 다른 FLOAT 값(.)보다 . 큽니다.

반올림 오류¶

부동 소수점 연산자는 최하위 자릿수에서 작은 반올림 오류가 발생할 수 있습니다. 반올림 오류는 삼각함수, 통계 및 지리공간 함수를 포함한 모든 유형의 부동 소수점 처리에서 발생할 수 있습니다.

The following list shows considerations for rounding errors:

오류는 쿼리가 실행될 때마다 다를 수 있습니다.
피연산자의 정밀도 또는 소수 자릿수가 다른 경우 더 큰 오류가 발생할 수 있습니다.
Errors can accumulate, especially when aggregate functions —for example, SUM or AVG — process large numbers of rows. Casting to a fixed-point data type before aggregating can reduce or eliminate these errors.
Rounding errors can occur not only when working with SQL, but also when working with other code — for example, Java, JavaScript, or Python — that runs inside Snowflake — for example, in UDFs and stored procedures.
두 개의 부동 소수점 숫자를 비교할 때 Snowflake는 정확히 같음이 아닌 대략적으로 같음을 사용하여 비교하는 것을 권장합니다.

It might be possible to avoid these types of approximation errors by using the exact DECFLOAT data type.

DOUBLE , DOUBLE PRECISION , REAL¶

FLOAT와 동의어입니다.

테이블에 있는 부동 소수점 데이터 타입의 예¶

다음 문은 다양한 부동 소수점 데이터 타입의 열이 있는 테이블을 생성합니다.

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

DESC TABLE test_float;

Copy

+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| 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           |
+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

참고

The DESC TABLE command’s type column displays the data type FLOAT not only for FLOAT, but also for synonyms of FLOAT; for example, DOUBLE, DOUBLE PRECISION, and REAL.

DECFLOAT¶

The decimal float (DECFLOAT) data type stores numbers exactly, with up to 38 significant digits of precision, and uses a dynamic base-10 exponent to represent very large or small values. The exponent range is from -16383 to 16384, allowing values approximately between -10^(16384) and 10^(16384). The DECFLOAT data type supports a variable scale so that the scale varies depending on the specific value being stored. In contrast to the FLOAT data type, which represents values as approximations, the DECFLOAT data type represents exact values in the specified precision.

The DECFLOAT data type doesn’t support the following special values that are supported by the FLOAT data type: 'NaN' (not a number), 'inf' (infinity), and '-inf' (negative infinity).

Use cases for the DECFLOAT data type¶

Use the DECFLOAT data type when you need exact decimal results and a wide, variable scale in the same column.

The DECFLOAT data type is appropriate for the following general use cases:

You are ingesting data, and the scale of incoming numeric values is unknown or highly variable.
You require exact numeric values; for example, ledgers, taxes, or compliance.
You are migrating from systems that rely on the IEEE 754-decimal representation or 128-bit decimals. These migrations might be blocked by the precision or range limitations of other Snowflake data types.
You want to avoid Number out of representable range errors when you sum, multiply, or divide high-precision numeric values.

For example, you can use the DECFLOAT data type for the following specific use cases:

You are ingesting heterogeneously scaled data from Oracle DECIMAL or DB2 DECFLOAT columns.
You are performing financial modeling that involves computations with scales of results that are hard to predict.
You are running scientific measurements that swing from nano units to astronomical units.

You can continue to use the NUMBER data type for fixed-scale numeric columns or the FLOAT data type for high-throughput analytics where imprecise results are acceptable.

Usage notes for the DECFLOAT data type¶

If an operation produces a result with more than 38 digits, the DECFLOAT value is rounded to 38-digit precision, with the least-significant digits rounded off according to the current rounding mode. Snowflake uses the half up rounding mode for DECFLOAT values.
When you specify a DECFLOAT value or you cast to a DECFLOAT value, avoid using numeric literals in SQL. If you use numeric literals in SQL, the values are interpreted as NUMBER or FLOAT values before being cast to a DECFLOAT value, which can result in range errors or loss of exactness. Instead, use either string literals — such as SELECT '<value>'::DECFLOAT — or the DECFLOAT literal — such as SELECT DECFLOAT '<value>'.
When operations mix DECFLOAT values and values of other numeric types, coercion prefers the DECFLOAT values. For example, when you add a value of NUMBER type and DECFLOAT type, the result is a DECFLOAT value.
Use of the DECFLOAT type might cause storage consumption to increase.

Drivers and driver versions that support the DECFLOAT data type¶

The following Snowflake drivers and driver versions support the DECFLOAT data type. You might need to update your drivers to the versions that support DECFLOAT:

Driver	Minimum supported version	Notes
Snowflake Connector for Python	3.14.1	pandas DataFrames don’t support the DECFLOAT type.
ODBC	3.12.0	None.
JDBC	3.27.0	None.
Go Snowflake Driver	1.17.0	None.
SQL API	2.0.0	None.

Unsupported drivers treat DECFLOAT values as TEXT values. For some drivers, a driver parameter must be set to map the DECFLOAT type to a language-native type. For more information, see 드라이버.

Limitations for the DECFLOAT data type¶

The following limitations apply to the DECFLOAT type:

DECFLOAT values can’t be stored in VARIANT, OBJECT, or ARRAY values. To cast a DECFLOAT value to a VARIANT value, you can first cast it to a VARCHAR value, and then cast it to a VARIANT value.
DECFLOAT values aren’t supported in the following types of tables:
- Tables in external formats, such as Iceberg
- Hybrid tables
The DECFLOAT data type isn’t supported in Snowflake Scripting stored procedures. However, it is supported in Snowflake Scripting user-defined functions (UDFs).
The DECFLOAT data type isn’t supported in stored procedures or UDFs written in a language other than SQL, such as Python or Java.
The DECFLOAT data type isn’t supported in Snowpark.
Snowsight has limited support for the DECFLOAT data type.
The following features don’t support the DECFLOAT data type:
The NUMBER and FLOAT types might provide better performance than the DECFLOAT type.

Examples for the DECFLOAT data type¶

The following examples use the DECFLOAT data type:

Show the differences between DECFLOAT and FLOAT
Use DECFLOAT values with aggregate functions

Show the differences between DECFLOAT and FLOAT¶

The following example shows the differences between the DECFLOAT and FLOAT data types:

Create a table with a DECFLOAT column and a FLOAT column, and then insert the same values for both types into the table:

CREATE OR REPLACE TABLE decfloat_sample (
  id INT,
  decfloat_val DECFLOAT,
  float_val FLOAT);

INSERT INTO decfloat_sample VALUES
  (
    1,
    DECFLOAT '123e7000',
    FLOAT '123e7000'
  ),
  (
    2,
    12345678901234567890123456789::DECFLOAT,
    12345678901234567890123456789::FLOAT
  ),
  (
    3,
    '-4.2e-5432'::DECFLOAT,
    '-4.2e-5432'::FLOAT
  ),
  (
    4,
    '1.00000000000000000000000000000000000014'::DECFLOAT,
    '1.00000000000000000000000000000000000014'::FLOAT
  ),
  (
    5,
    '1.00000000000000000000000000000000000015'::DECFLOAT,
    '1.00000000000000000000000000000000000015'::FLOAT
  );

Copy

The statement inserts DECFLOAT values in the following ways:

The first value is inserted by using the DECFLOAT literal.
The second value is inserted by casting an INTEGER value to a DECFLOAT value.
The third, fourth, and fifth values are inserted by casting a VARCHAR value to a DECFLOAT value.

To show the types, describe the table by using the DESC TABLE command.

The precision wasn’t specified in the table definition for either column, but the output shows that the DECFLOAT data type supports up to 38 significant digits of precision:

DESC TABLE decfloat_sample;

Copy

+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name         | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| ID           | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DECFLOAT_VAL | DECFLOAT(38) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FLOAT_VAL    | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

To show the differences in the values, query the table by using the SELECT statement:
```
SELECT * FROM decfloat_sample;
```
Copy
```
+----+-----------------------------------------+------------------------+
| ID | DECFLOAT_VAL                            |              FLOAT_VAL |
|----+-----------------------------------------+------------------------|
|  1 | 1.23e7002                               | inf                    |
|  2 | 12345678901234567890123456789           |   1.23456789012346e+28 |
|  3 | -4.2e-5432                              |  -0                    |
|  4 | 1.0000000000000000000000000000000000001 |   1                    |
|  5 | 1.0000000000000000000000000000000000002 |   1                    |
+----+-----------------------------------------+------------------------+
```
The output shows the following differences:
- The first row shows that the DECFLOAT type supports a wider range of values than the FLOAT type. The DECFLOAT value is very large (1.23e7002). The FLOAT value is inf, which means that the value is larger than any value that the FLOAT type can represent.
- The second row shows that the DECFLOAT type retains the specified value exactly. The FLOAT value is an approximation that is stored in scientific notation.
- The third row shows that the DECFLOAT type supports very small values (-4.2e-5432). The FLOAT value is approximated to -0.
- The fourth and fifth rows show that the DECFLOAT type supports up to 38 digits of precision and uses rounding rules for values beyond the limit. The FLOAT value is approximated to 1 in both rows.

Use DECFLOAT values with aggregate functions¶

The following example uses DECFLOAT values with aggregate functions:

Create a table, and then insert DECFLOAT values into the table:

CREATE OR REPLACE TABLE decfloat_agg_sample (decfloat_val DECFLOAT);

INSERT INTO decfloat_agg_sample VALUES
  (DECFLOAT '1e1000'),
  (DECFLOAT '-2.47e999'),
  (DECFLOAT '22e-75');

Copy

Query the table by using some aggregate functions:

SELECT SUM(decfloat_val),
       AVG(decfloat_val),
       MAX(decfloat_val),
       MIN(decfloat_val)
  FROM decfloat_agg_sample;

Copy

+-------------------+-------------------+-------------------+-------------------+
| SUM(DECFLOAT_VAL) | AVG(DECFLOAT_VAL) | MAX(DECFLOAT_VAL) | MIN(DECFLOAT_VAL) |
|-------------------+-------------------+-------------------+-------------------|
| 7.53e999          | 2.51e999          | 1e1000            | -2.47e999         |
+-------------------+-------------------+-------------------+-------------------+

숫자 상수¶

The term constants — also known as literals — refers to fixed data values. The following formats are supported for numeric constants:

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

여기서:

+ 또는 - 는 양수 또는 음수 값을 나타냅니다. 기본값은 양수입니다.
digits 는 0에서 9까지의 숫자 중 하나 이상의 숫자입니다.
e (또는 E)는 과학적 표기법의 지수를 나타냅니다. 지수 표시가 있는 경우 하나 이상의 숫자가 지수 표시 뒤에 와야 합니다.

다음 숫자는 지원되는 숫자 상수의 모든 예입니다.

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