FirebirdSQL logo

DECFLOAT

Data Type Declaration Format
DECFLOAT [(dec_prec)]
Table 1. DECFLOAT Type Parameters
Parameter 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.

16-digit and 34-digit

The “16” and “34” refer to the maximum precision in Base-10 digits.See https://en/wikipedia.org/wiki/iEEE_754#Basic_and_interchange_formats for a comprehensive table.

Table 2. Range of Values
Type Maximum precision Minimum Exponent Maximum Exponent Smallest value Largest value

DECFLOAT(16)

16

-383

+384

1E-398

9.9..9E+384

DECFLOAT(34)

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.

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.

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.

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:

ABS

CEILING

EXP

FLOOR

LN

LOG

LOG10

POWER

SIGN

SQRT

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.