## 3.2. Floating-Point Data Types

Firebird supports two types of floating-point data types: approximate or binary floating-point data types (`FLOAT`

and `DOUBLE PRECISION`

), and decimal floating-point types (`DECFLOAT`

).

### 3.2.1. Approximate Floating-Point Data Types

Approximate floating-point values are stored in an IEEE 754 binary format that comprises sign, exponent and mantissa.
Precision is dynamic, corresponding to the physical storage format of the value, which is exactly 4 bytes for the `FLOAT`

type and 8 bytes for `DOUBLE PRECISION`

.

Considering the peculiarities of storing floating-point numbers in a database, these data types are not recommended for storing monetary data. For the same reasons, columns with floating-point data are not recommended for use as keys or to have uniqueness constraints applied to them.

For testing data in columns with floating-point data types, expressions should check using a range, for instance, `BETWEEN`

, rather than searching for exact matches.

When using these data types in expressions, extreme care is advised regarding the rounding of evaluation results.

#### 3.2.1.1. `FLOAT`

Data Type Declaration Format

` |``FLOAT [(`*bin_prec*)]

`FLOAT`

Type ParametersParameter | Description |
---|---|

bin_prec | Precision in binary digits, default is 24 1 - 24: 32-bit single precision 25 - 53: 64-bit double precision |

The `FLOAT`

data type defaults to a 32-bit single precision floating-point type with an approximate precision of 7 decimal digits after the decimal point (24 binary digits).
To ensure the safety of storage, rely on 6 decimal digits of precision.

The syntax `FLOAT(`

behaves as follows:*bin_prec*)

1 <= _bin_prec <= 23: 32-bit single precision (synonym for

`FLOAT`

)25 <= _bin_prec <= 53: 64-bit double precision (synonym for

`DOUBLE PRECISION`

)

The behaviour of `FLOAT`

(without explicit precision) behaves as the SQL standard type Section 3.2.1.2, “`REAL`

”.

Firebird 3.0 and earlier supported

`FLOAT(`

where*dec_prec*)*dec_prec*was the approximate precision in decimal digits, with 0 <=*dec_prec*<= 7 mapped to 32-bit single precision and*P*> 7 mapped to 64-bit double precision. This syntax was never documented.For

*bin_prec*in`FLOAT(`

, the values 1 <=*bin_prec*)*bin_prec*<= 24 are all treated as*bin_prec*= 24, values 25 <=*bin_prec*<= 53 are all handled as*bin_prec*= 53.Most Firebird tools will report

`FLOAT(1)`

—`FLOAT(24)`

as`FLOAT`

, and`FLOAT(25)`

—`FLOAT(53)`

as`DOUBLE PRECISION`

.

#### 3.2.1.2. `REAL`

Data Type Declaration Format

` |``REAL`

The data type `REAL`

is a synonym for `FLOAT`

, and is provided for syntax compatibility.
When used to define a column or parameter, it’s indistinguishable from using `FLOAT`

or `FLOAT(1)`

— `FLOAT(24)`

.

`REAL`

has been available as a synonym for`FLOAT`

since Firebird 1.0 and even earlier, but was never documented.Most Firebird tools will report

`FLOAT`

instead of`REAL`

.

#### 3.2.1.3. `DOUBLE PRECISION`

Data Type Declaration Format

` |``DOUBLE PRECISION`

The `DOUBLE PRECISION`

data type is stored with an approximate precision of 15 digits.

Firebird also has the — previously undocumented — synonyms for

`DOUBLE PRECISION`

:`LONG FLOAT`

and`LONG FLOAT(`

, with 1 <=*bin_prec*)*bin_prec*<= 53.These non-standard type names are deprecated and may be removed in a future Firebird version.

Firebird 3.0 and earlier supported

`LONG FLOAT(`

where*dec_prec*)*dec_prec*was the approximate precision in decimal digits, where any value for*dec_prec*mapped to 64-bit double precision.

### 3.2.2. Decimal Floating-Point Types

Decimal floating-point values are stored in an IEEE 754 decimal format that comprises sign, exponent and coefficient. Contrary to the approximate floating-point data types, precision is either 16 or 34 decimal digits.

#### 3.2.2.1. `DECFLOAT`

Data Type Declaration Format

` |``DECFLOAT [(`*dec_prec*)]

`DECFLOAT`

Type ParametersParameter | Description |
---|---|

dec_prec | Precision in decimal digits, either 16 or 34. Default is 34. |

`DECFLOAT`

is a SQL standard-compliant numeric type that stores floating-point number precisely (decimal floating-point type), unlike `FLOAT`

or `DOUBLE PRECISION`

that provide a binary approximation of the purported precision.

The type is stored and transmitted as IEEE 754 standard types `Decimal64`

(`DECFLOAT(16)`

) or `Decimal128`

(`DECFLOAT(34)`

).

All intermediate calculations are performed with 34-digit values.

Type | Maximum precision | Minimum Exponent | Maximum Exponent | Smallest value | Largest value |
---|---|---|---|---|---|

| 16 | -383 | +384 | 1E-398 | 9.9..9E+384 |

| 34 | -6143 | +6144 | 1E-6176 | 9.9..9E+6144 |

Observe that although the smallest exponent for `DECFLOAT(16)`

is -383, the smallest value has an exponent of -398, but 15 fewer digits.
And similar for `DECFLOAT(34)`

, smallest exponent is -6143, but the smallest value has an exponent of -6176, but 33 fewer digits.
The reason is that precision was sacrificed

to be able to store a smaller value.

This is a result of how the value is stored: as a decimal value of 16 or 34 digits and an exponent.
For example, `1.234567890123456e-383`

is stored as coefficient `1234567890123456`

and exponent `-398`

, while `1E-398`

is stored as coefficient `1`

, exponent `-398`

.

##### 3.2.2.1.1. Behaviour of `DECFLOAT`

Operations

The behaviour of `DECFLOAT`

operations in a session, specifically rounding and error behaviour, can be configured using the `SET DECFLOAT`

management statement, and the `isc_dpb_decfloat_round`

and `isc_dpb_decfloat_traps`

DPB items.

##### 3.2.2.1.2. Length of `DECFLOAT`

Literals

It is possible to express `DECFLOAT(34)`

values in approximate numeric literals, but only for values with a mantissa of 20 or more digits, or an absolute exponent larger than 308.
Scientific notation literals with fewer digits or a smaller absolute exponent are `DOUBLE PRECISION`

literals.
Exact numeric literals with 40 or more digits — actually 39 digits, when larger than the maximum `INT128`

value — are also handled as `DECFLOAT(34)`

.

Alternatively, use a string literal and explicitly cast to the desired `DECFLOAT`

type.

The length of `DECFLOAT`

literals cannot exceed 1024 characters.
Scientific notation is required for greater values.
For example, `0.0<1020 zeroes>11`

cannot be used as a literal, but the equivalent in scientific notation, `1.1E-1022`

, is valid.
Similarly, `10<1022 zeroes>0`

can be presented as `1.0E1024`

.
Literals with more than 34 significant digits are rounded using the `DECFLOAT`

rounding mode of the session.

##### 3.2.2.1.3. `DECFLOAT`

and Functions

##### Use with Standard Functions

A number of standard scalar functions can be used with expressions and values of the `DECFLOAT`

type.
They are:

The aggregate functions `SUM`

, `AVG`

, `MAX`

and `MIN`

work with `DECFLOAT`

data, as do all the statistical aggregates (including but not limited to `STDDEV`

or `CORR`

).

##### Special Functions for `DECFLOAT`

Firebird supports four functions, designed to support `DECFLOAT`

data specifically:

`COMPARE_DECFLOAT`

compares two

`DECFLOAT`

values to be equal, different or unordered`NORMALIZE_DECFLOAT`

takes a single

`DECFLOAT`

argument and returns it in its simplest form`QUANTIZE`

takes two

`DECFLOAT`

arguments and returns the first argument scaled using the second value as a pattern`TOTALORDER`

performs an exact comparison on two

`DECFLOAT`

values

Detailed descriptions are available in the Special Functions for `DECFLOAT`

section of the *Built-in Scalar Functions* chapter.