A floating-point number can have zero decimal point or one decimal point after any digit. This topic describes the syntax, parameters, and considerations of floating-point numbers.
Syntax
OceanBase Database provides two numeric data types for floating-point numbers:
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.
- 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
SQRTreturns the square root of a number. - The SQL function
REMAINDERreturns 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, andBINARY_DOUBLE_NAN. - SQL functions such as
ROUND,TRUNC,CEIL, andFLOORround 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, andCASTround 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.
- The SQL function
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
NaNis not supported. - Nondefault rounding modes are not supported.
- Nondefault exception handling modes are not supported.