Numeric data types

Numeric data types use numeric literals to specify fixed and floating-point numbers. Some numeric data types also support zero, infinity, and not-a-number (NaN) values. This topic describes the NUMBER type and floating-point numbers.

NUMBER

The NUMBER type stores both fixed-point and floating-point numbers. This type can store numbers of almost any magnitude.

• Precision:

  Precision specifies the total number of digits of a number. If precision is not specified, the original number provided is stored without rounding.

• Scale:

  Scale specifies the number of digits to the right of the decimal point, down to the least significant digit. Positive scale counts the digits to the right of the decimal point until the least significant digit. Negative scale counts the digits to the left of the decimal point. If you specify a precision without a scale, for example, NUMBER(6), the scale is considered as 0.

A numeric literal can store a number with a maximum precision of 38 digits. If the precision of a literal is higher than that supported by the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type, OceanBase Database truncates the literal. If the range of a literal exceeds the boundaries supported by the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type, OceanBase Database returns an error.

For example, NUMBER(8,2) indicates a precision of 8 and a scale of 2. Therefore, the database stores 100,000 as 100000.00.

Sample valid values of the NUMBER type are as follows:

12
+6.87
0.5
25e-03
-9

Sample valid values of the floating-point type are as follows:

25f
+6.34F
0.5d
-1D

Floating-point numbers

OceanBase Database provides two numeric data types exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE.

These types support all basic features provided by the NUMBER type. However, the NUMBER type uses decimal precision, whereas the BINARY_FLOAT and BINARY_DOUBLE types use binary precision. Binary precision enables faster arithmetic calculations and usually reduces storage requirements.

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric data types. They store approximate representations, rather than exact representations, of decimal values. For example, the value 0.1 cannot be exactly represented by the BINARY_DOUBLE or BINARY_FLOAT type. These types are often used in scientific calculations. They function like the FLOAT and DOUBLE types in Java.

Sample valid floating-point numbers are as follows:

1.2
-1.1
0.

In addition to the NUMBER and floating-point types, numeric data types also support special representations such as positive infinity, negative infinity, and NaN representations. The following special representations are supported:

• binary_double_infinity

• binary_double_nan

• binary_float_infinity

• binary_float_nan