A floating-point number can have zero or one decimal point after any digit. This topic describes the syntax, parameters, and considerations of floating-point numbers.
Syntax
OceanBase Database provides two types of floating-point numbers for numeric data: BINARY_FLOAT and BINARY_DOUBLE.
BINARY_FLOATis a data type for 32-bit single-precision floating-point numbers. Each value of theBINARY_FLOATtype occupies four bytes.BINARY_DOUBLEis a data type for 64-bit double-precision floating-point numbers. Each value of theBINARY_DOUBLEtype occupies eight bytes.Note
- In a
NUMBERcolumn, floating-point numbers have decimal precision. - In a
BINARY_FLOATorBINARY_DOUBLEcolumn, floating-point numbers have binary precision. - Binary floating-point numbers do not support special values
infinityandNaN.
- In a
Value range
You can specify a floating-point number based on its value range listed in the following table.
| Value | BINARY_FLOAT | BINARY_DOUBLE |
|---|---|---|
| Maximum positive value | 3.40282E+38F | 1.79769313486231E+308 |
| Minimum positive value | 1.17549E-38F | 2.22507485850720E-308 |
Considerations
You can use an exponent to increase the range of a floating-point number, such as 1.666 e-20. The decimal digits do not apply to floating-point numbers, because the number of digits that can appear on the right of the decimal point is not limited.
Notice
Unlike data of the NUMBER type, binary floating-point numbers are stored within OceanBase Database. NUMBER data is accurately stored in decimal precision. Binary floating-point numbers are stored in binary precision and therefore do not accurately represent values in decimal precision.
IEEE 754 compliance
IEEE 754-1985 (IEEE Standard for Binary Floating-Point Arithmetic) OceanBase Database implements floating-point data types in basic compliance with the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic.
The floating-point data types comply with IEEE 754 in the following aspects:
- The SQL function SQRT returns the square root of a number.
- The SQL function REMAINDER returns the remainder of a number.
- Arithmetic operators are consistent.
- Comparison operators meet the requirements.
- Conversion operators meet the requirements.
- The default rounding mode is supported.
- The default exception handling mode is supported.
- OceanBase Database does not support special constants such as INF, -INF, NaN, BINARY_FLOAT_NAN, and BINARY_DOUBLE_NAN.
- SQL functions such as ROUND, TRUNC, CEIL, and FLOOR round binary floating-point numbers of the BINARY_FLOAT or BINARY_DOUBLE type to integers of the BINARY_ FLOAT or BINARY_DOUBLE type.
- SQL functions such as TO_CHAR, TO_NUMBER, TO_ NCHAR, TO_BINARY_FLOAT, TO_BINARY_DOUBLE, and CAST round binary floating-point numbers of the BINARY_FLOAT or BINARY_DOUBLE type and decimal numbers to decimal numbers of the BINARY_FLOAT or BINARY_DOUBLE type.
OceanBase Database does not comply with IEEE 754 for floating-point data types in the following aspects:
- -0 is forced to be +0.
- Comparison against NaN is not supported.
- The nondefault rounding mode is not supported.
- The nondefault exception handling mode is not supported.
