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.
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.
DECFLOAT
DECFLOAT [(dec_prec)]
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.
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.
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
.
DECFLOAT
OperationsThe 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.
DECFLOAT
LiteralsIt 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.