FLOAT data type

The FLOAT data type is a subtype of the NUMBER data type. You can specify the precision for the FLOAT data type. The precision of the FLOAT data type is the same as that of the NUMBER data type. This topic describes the syntax, parameters, and usage of the FLOAT data type.

Syntax

FLOAT [(p)]

Parameters

Considerations

The FLOAT data type is a subtype of the NUMBER data type with precision. It occupies 4 to 40 bytes of storage space. The precision of the FLOAT data type is calculated in terms of binary significant digits and ranges from 1 to 126. You cannot customize the number of decimal digits. The FLOAT data type is a variable-length, imprecise data type.

Examples

• Set the binary precision of the FLOAT data type to 2. The decimal precision is calculated as int(2 x 0.30103) = 0.6, which is rounded down to 0. Therefore, the decimal precision of the FLOAT(2) data type is 0.

  FLOAT(2)
  

• Create a table named test and insert data into it. The col1 column is of the NUMBER data type, and the col2 column is of the FLOAT data type. NUMBER(5,2) indicates a fixed-point number with a decimal precision of 5, where the result is rounded to two decimal places. The binary precision of the FLOAT(5) data type is 5, and the decimal precision is calculated as int(5 x 0.30103) = 1.50515, which is rounded down to 1. For example, 123.45 can be represented in scientific notation as 1.2345 x 10^2^, and the decimal part is rounded to one digit, resulting in 1.2. The final result is 1.2 x 10^2^ = 120. Execute the following statement:

  CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
  INSERT INTO test VALUES (1.23, 1.23);
  INSERT INTO test VALUES (7.89, 7.89);
  INSERT INTO test VALUES (12.79, 12.79);
  INSERT INTO test VALUES (123.45, 123.45);
  

  Execute the SELECT statement to query the test table. The returned result is as follows:

  obclient> SELECT * FROM test;
  +--------+------+
  | COL1   | COL2 |
  +--------+------+
  |   1.23 |  1.2 |
  |   7.89 |  7.9 |
  |  12.79 |   13 |
  | 123.45 |  120 |
  +--------+------+
  4 rows in set
  

  Note

  When you convert ANSI FLOAT data, you can use the FLOAT data type used internally by OceanBase Database. However, we recommend that you use the BINARY_FLOAT and BINARY_DOUBLE floating-point data types.