Floating-Point Data Types - Firebird

Floating-point types employ a sort of “sliding window” of precision appropriate to the scale of the number. By the nature of “float” types, the placement of the decimal point is not a restriction—it is valid to store, in the same column, one value as 25.33333 and another as 25.333. The values are different and both are acceptable.

Define a floating-point column when you need to store numbers of varying scale. The general rule of thumb for choosing a floating-point, rather than a fixed-decimal type, is “use them for values you measure, not for values you count.” If a floating-point column or variable must be used to store money, you should pay careful attention both to rounding issues and to testing the results of calculations.

Floating-point numbers can be used to represent a value over a much larger range than is possible with plain or scaled integers. For example, the FLOAT type can carry values with a magnitude as large as 3.4E38 (that’s 34 followed by 37 zeros) and as small as 1.1E-38 (that’s 11 preceded by 37 zeros and then a decimal point).

The breadth of range is achieved by a loss in exactness. A floating-point number carries an approximate representation of its value that is accurate for a specific number of digits (its precision), according to the current magnitude (scale). It cannot carry a value close to either extreme of its range.

The floating-point value carries more information than the stated number digits of precision. The FLOAT type, for example, is said to have a precision of 7 digits but its accurate precision is 6 digits. The last part is an approximation providing additional information about the number, such as an indicator for rounding and some more things that are important when arithmetic is performed on the number.

For example, a FLOAT can carry the value 1000000000 (1,000,000,000, or 109). The FLOAT “container” sees this value as (effectively) 100000*E4. (This is illustrative only— an authoritative exposition of floating-point implementation is beyond the scope of this book and seriously beyond the author’s reach!) If you add 1 to the value of the FLOAT, it will ignore the additional information carried in the seventh digit, because it is not significant in terms of the number’s current magnitude and the precision available. If you add 10,000—a value that is significant to the magnitude of the number currently stored in the FLOAT—it can represent the result as 100001*E4.

Even values within the available precision of the floating-point number may not always store an exact representation. A value such as 1.93 or even 123 may be represented in storage as a value that is very close to the specific number. It is close enough that, when the floating-point number is rounded for output, it will display the value expected and, when it is used in calculations, the result is a very close approximation to the expected result.

The effect is that, when you perform some calculation that should result in the value 123, it may only be a very close approximation to 123. Exact comparisons (equality, greater than, less than, and so on) between two floating-point numbers, or between a floating-point number and zero, or a floating-point number and a fixed type, thus cannot be depended on to produce the expected results.

For this reason, do not consider using floating-point columns in keys or applying uniqueness constraints to them. They will not work predictably for foreign key relationships or joins.

For comparisons, test floating-point values as being BETWEEN some acceptable range rather than testing for an exact match. The same advice applies when testing for 0—choose a range appropriate to the magnitude and signing of your data that is between zero and a near-zero value, or between two suitable near-zero values.

In a dialect 1 database, the need to store numeric data values having a wider range than the limits of a 32-bit integer may force the choice of a DOUBLE PRECISION type. Dialect 1 limitations also require the use of floating-point numbers for all reals if the database is going to be accessed by an embedded (ESQL) application.

Firebird provides two floating-point or approximate numeric data types, FLOAT and DOUBLE PRECISION, differing only in the limit of precision.

FLOAT

FLOAT is a 32-bit floating-point data type with a limit of approximately 7 digits of precision—assume 6 digits for reliability. A 10-digit number 25.33333312 inserted into a FLOAT column is stored as 25.33333. The range is from –3.402×1038 to 3.402×1038.

The smallest positive number it can store is 1.175×10 –38.

DOUBLE PRECISION

DOUBLE PRECISION is a 64-bit floating-point data type with a limit of approximately 15 digits of precision. The range is from 308 –1.797×10308 to 1.797×10308. The smallest positive number it can store is 2.225×10–308.

Arithmetic Mixing Fixed and Floating-Point Types

When a dyadic operation (addition, subtraction, multiplication, division) involves an
exact numeric operand and a floating-point operand, the result will be a DOUBLE PRECISION type.

The next statement creates a column, PERCENT_CHANGE, using a DOUBLE PRECISION type:

The following CREATE TABLE statement provides an example of how the different numeric types can be used: an INTEGER for the total number of orders, a fixed DECIMAL for the dollar value of total sales, and a FLOAT for a discount rate applied to the sale.

Firebird Topics