Floating-point numbers can have a decimal point, from the first digit to the last, or none at all. This topic describes the syntax, parameters, and usage of floating-point numbers.
Syntax
OceanBase Database provides two numeric data types for floating-point numbers:
BINARY_FLOAT, a 32-bit single-precision floating-point number data type. EachBINARY_FLOATvalue requires 4 bytes.BINARY_DOUBLE, a 64-bit double-precision floating-point number data type. EachBINARY_DOUBLEvalue requires 8 bytes.Note
- In
NUMBERcolumns, floating-point numbers have decimal precision. - In
BINARY_FLOATorBINARY_DOUBLEcolumns, floating-point numbers have binary precision.
- In
Value range
You can specify floating-point numbers based on the value range in the following table.
| Value | BINARY_FLOAT | BINARY_DOUBLE |
|---|---|---|
| Maximum positive finite value | 3.40282E+38F | 1.79769313486231E+308 |
| Minimum positive finite value | 1.17549E-38F | 2.22507485850720E-308 |
Usage
Floating-point numbers can be used with exponents to increase their range, such as 1.666e-20. Floating-point numbers do not have a fixed number of decimal places, as the number of digits after the decimal point is not limited.
Notice
The difference between binary floating-point numbers and the NUMBER data type lies in how values are stored. The NUMBER data type stores values using decimal precision, ensuring exact storage. Binary floating-point numbers (FLOAT-Point Numbers), on the other hand, are stored using binary precision (digits 0 and 1). This storage method cannot accurately represent all values that can be expressed with decimal precision.
IEEE 754 compliance
IEEE Standard 754-1985 (IEEE 754)
OceanBase Database's implementation of floating-point data types is largely compliant with the IEEE binary floating-point arithmetic standard.
Floating-point data types comply with IEEE 754 in the following aspects:
- The SQL function SQRT computes square roots.
- The SQL function REMAINDER computes remainders.
- Arithmetic operators are consistent.
- Comparison operators meet the requirements.
- Conversion operators are supported.
- Default rounding modes are supported.
- Default exception handling modes are supported.
- OceanBase Database does not support special constants such as INF, -INF, NaN, BINARY_FLOAT_NAN, and BINARY_DOUBLE_NAN at this time.
- The SQL functions ROUND, TRUNC, CEIL, and FLOOR provide the capability to round binary floating-point numbers BINARY_FLOAT and BINARY_DOUBLE to integer values BINARY_FLOAT and BINARY_DOUBLE.
- The SQL functions TO_CHAR, TO_NUMBER, TO_NCHAR, TO_BINARY_FLOAT, TO_BINARY_DOUBLE, and CAST provide the capability to round binary floating-point numbers BINARY_FLOAT and BINARY_DOUBLE to decimal numbers and to round decimal numbers to BINARY_FLOAT and BINARY_DOUBLE.
Floating-point data types do not comply with IEEE 754 in the following aspects:
- -0 is forcibly converted to +0.
- Comparisons with NaN are not supported.
- Non-default rounding modes are not supported.
- Non-default exception handling modes are not supported.