DECIMAL and NUMERIC Data Types

Go Up to Features and Dialects

The following sections highlight some of the changes introduced by InterBase 6 and later when dealing with numeric values. They need to be considered carefully when migrating your database from dialect 1 to dialect 3. When considering these issues, keep in mind that in order to make use of the new functionality, the statements must be created with a client dialect setting of 3.

The most notable migration issues involve using the division operator and the AVG() function (which also implies division) with exact numeric operands. Exact numeric refers to any of the following data types: INTEGER, SMALLINT, DECIMAL, NUMERIC. NUMERIC and DECIMAL data types that have a precision greater than 9 are called “large exact numerics” in this discussion. Large exact numerics are stored as DOUBLE PRECISION in dialect 1 and as INT64 in columns created in dialect 3.

Important:
When you migrate an exact numeric column to dialect 3 it is still stored as DOUBLE PRECISION. The migration does not change the way the data is stored because INT64 cannot store the whole range that DOUBLE PRECISION can store. There is potential data loss, so InterBase does not permit direct conversion. If you decide that you want your data stored as INT64, you must create a new column and copy the data. Only exact numeric columns that are created in dialect 3 are stored as INT64. The details of the process are provided in Migrating Databases to Dialect 3.

You might or might not want to change exact numeric columns to INT64 when you migrate to dialect 3. See Do you really need to migrate your NUMERIC and DECIMAL Data Types? for a discussion of issues.

Dialect 3 features and changes include

Support for 64 bit integers.
Overflow protection. In dialect 1, if the product of two integers was bigger than 31 bits, the product was returned modulo 2³². In dialect 3, the true result is returned as a 64-bit integer. Further, if the product, sum, difference, or quotient of two exact numeric values is bigger than 63 bits, InterBase issues an arithmetic overflow error message and terminates the operation. (Previous versions sometimes returned the least-significant portion of the true result.). The stored procedure bignum below demonstrates this.

Operations involving division return an exact numeric if both operands are exact numerics in dialect 3. When the same operation is performed in dialect 1, the result is a DOUBLE PRECISION.

To obtain a DOUBLE PRECISION quotient of two exact numeric operands in dialect 3, explicitly cast one of the operands to DOUBLE PRECISION before performing the division:

CREATE TABLE table 1 (n1 INTEGER, n2 INTEGER);
INSERT INTO table 1 (n1, n2) VALUES (2, 3);
SELECT n1 / n2 FROM table1;
======================
0

Similarly, to obtain a double precision value when averaging an exact numeric column, you must cast the argument to double precision before the average is calculated:

SELECT AVG(CAST(int_col AS DOUBLE PRECISION))FROM table1;

Advance To:

Compiled Objects