Floating-point Data Types

Go Up to Defining Numeric Data Types

InterBase provides two floating-point data types, FLOAT and DOUBLE ­PRECISION; the only difference is their size. FLOAT specifies a single-precision, 32-bit data type with a precision of approximately 7 decimal digits. DOUBLE PRECISION specifies a double-precision, 64-bit data type with a precision of approximately 15 decimal digits.

The precision of FLOAT and DOUBLE PRECISION is fixed by their size, but the scale is not, and you cannot control the formatting of the scale. With floating numeric data types, the placement of the decimal point can vary; the position of the decimal is allowed to “float.” For example, in the same column, one value could be stored as 25.33333, and another could be stored as 25.333.

Use floating-point numbers when you expect the placement of the decimal point to vary, and for applications where the data values have a very wide range, such as in scientific calculations.

If the value stored is outside of the range of the precision of the floating-point number, then it is stored only approximately, with its least-significant digits treated as zeros. For example, if the type is FLOAT, you are limited to 7 digits of precision. If you insert a 10-digit number 25.33333312 into the column, it is stored as 25.33333.

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

CREATE TABLE SALARY_HISTORY
(. . .
PERCENT_CHANGE DOUBLE PRECISION
DEFAULT 0
NOT NULL
CHECK (PERCENT_CHANGE BETWEEN -50 AND 50),
. . .);

You can perform the following operations on FLOAT and DOUBLE PRECISION data types:

• Comparisons using the standard relational operators (=, <, >, >=, <=). Other operators such as CONTAINING, STARTING WITH, and LIKE perform string comparisons on the integer portion of floating data.
• Arithmetic operations. The standard arithmetic operators determine the sum, difference, product, or dividend of two or more integers.
• Conversions. When performing arithmetic operations that involve mixed data types, InterBase automatically converts between INTEGER, FLOAT, and CHAR data types. For operations that involve comparisons of numeric data with other data types, such as CHARACTER and INTEGER, InterBase first converts the data to a numeric type, then compares them numerically.
• Sorts. By default, a query retrieves rows in the exact order that it finds them in the table, which is likely to be unordered. Sort rows using the ORDER BY clause of a SELECT statement in descending or ascending order.

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.

CREATE TABLE SALES
(. . .
QTY_ORDERED INTEGER
DEFAULT 1
CHECK (QTY_ORDERED >= 1),
TOTAL_VALUE DECIMAL (9,2)
CHECK (TOTAL_VALUE >= 0),
DISCOUNT FLOAT
DEFAULT 0
CHECK (DISCOUNT >= 0 AND DISCOUNT <= 1));