Floating-point data types

The floating-point data types are used to store approximate numeric values with fixed lengths. The value range of a floating-point data type depends on the precision, scale, and whether the data type is unsigned.

The precision specifies the total maximum number of valid digits in a decimal value, and the scale specifies the maximum number of valid digits in the fractional part of the decimal value. You can obtain the maximum number of valid digits in the integer part by subtracting the scale from the precision. The maximum value of the precision is 255 and that of the scale is 30. When the precision is 255, the scale must be 0.

The following table describes the length and value range of each floating-point data type when the precision and scale are not specified.

If the precision and scale are specified, the value range is determined in the same way as that for the fixed-point data types.

FLOAT

The FLOAT type is used to represent a small (single-precision) floating-point number. The syntax is as follows:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

Here, M specifies the total number of digits in the value that can be stored, and D specifies the number of digits after the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number has about 7 decimal digits of precision.

If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column. If a column is defined as UNSIGNED, the column values cannot be negative.

The following FLOAT syntax is also supported:

FLOAT(p) [UNSIGNED] [ZEROFILL]

Here, p specifies the precision in bits. It is used only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT, without the M or D value. If p is from 25 to 53, the data type becomes DOUBLE, without the M or D value. The range of the resulting column is the same as that of the FLOAT or DOUBLE data type described in the preceding table.

DOUBLE

The DOUBLE data type is used to represent a normal-sized (double-precision) floating-point number. The syntax is as follows:

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

Here, M specifies the total number of digits in the value that can be stored, and D specifies the number of digits after the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number has about 15 decimal digits of precision.

If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column. If a column is defined as UNSIGNED, the column values cannot be negative.

DOUBLE PRECISION

The DOUBLE PRECISION data type is a synonym for the DOUBLE data type. The syntax is as follows:

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]