Fixed-decimal Data Types

Go Up to Defining Numeric Data Types

InterBase supports two SQL data types, NUMERIC and DECIMAL, for handling numeric data with a fixed decimal point, such as monetary values. You can specify optional precision and scale factors for both data types. These data types are also referred to as exact numerics.

• Precision is the total number or maximum number of digits, both significant and fractional, that can appear in a column of these data types. The allowable range for precision is from 1 to a maximum of 18.
• Scale is the number of digits to the right of the decimal point that comprise the fractional portion of the number. The allowable range for scale is from zero to precision; in other words, scale must be less than or equal to precision.

The syntax for NUMERIC and DECIMAL is as follows:

NUMERIC[(precision [, scale])]
DECIMAL[(precision [, scale])]

You can specify NUMERIC and DECIMAL data types without precision or scale, with precision only, or with both precision and scale.

NUMERIC data type

NUMERIC(x,y)

In the syntax above, InterBase stores exactly <x> digits. Of that number, exactly <y> digits are to the right of the decimal point. For example,

NUMERIC(5,2)

declares that a column of this type always holds numbers with exactly five digits, with exactly two digits to the right of the decimal point: ppp.ss.

DECIMAL data type

DECIMAL(x,y)

In the syntax above, InterBase stores at least <x> digits. Of that number, exactly <y> digits are to the right of the decimal point. For example,

DECIMAL(5,2)

declares that a column of this type must be capable of holding at least five but possibly more digits and exactly two digits to the right of the decimal point: ppp.ss.

How fixed-decimal Data Types are Stored

When you create a domain or column with a NUMERIC or DECIMAL data type, InterBase determines which data type to use for internal storage based on the precision and scale that you specify and the dialect of the database.

• NUMERIC and DECIMAL data types that are declared without either precision or scale are stored as INTEGER.
• Defined with precision, with or without scale, they are stored as SMALLINT, INTEGER, DOUBLE PRECISION or 64-bit integer. Storage type depends on both the precision and the dialect of the database.

Precision	Dialect 1	Dialect 3
1 to 4	SMALLINT for NUMERIC data types INTEGER for DECIMAL data types	SMALLINT
5 to 9	INTEGER	INTEGER
10 to 18	DOUBLE PRECISION	INT64

NUMERIC and DECIMAL data types with precision greater than 10 always produce an error when you create a dialect 2 database. This forces you to examine each instance during a migration. For more about migrating exact numerics, see Migrating Databases with NUMERIC and DECIMAL Data Types. For a broader discussion of migration issues, see the migration appendix in the InterBase Operations Guide.

The following table summarizes how InterBase stores NUMERIC and DECIMAL data types based on precision and scale:

NUMERIC and DECIMAL data type storage based on precision and scale

Data type specified as…	Data type stored as…
NUMERIC	INTEGER
NUMERIC(4)	SMALLINT
NUMERIC(9)	INTEGER
NUMERIC(10)	DOUBLE PRECISION in dialect 1 INT64 in dialect 3
NUMERIC(4,2)	SMALLINT
NUMERIC(9,3)	INTEGER
NUMERIC(10,4)	DOUBLE PRECISION in dialect 1 INT64 in dialect 3
DECIMAL	INTEGER
DECIMAL(4)	INTEGER
DECIMAL(9)	INTEGER
DECIMAL(10)	DOUBLE PRECISION in dialect 1 INT64 in dialect 3
DECIMAL(4,2)	INTEGER
DECIMAL(9,3)	INTEGER
DECIMAL(10,4)	DOUBLE PRECISION in dialect 1 INT64 in dialect 3

Specifying NUMERIC and DECIMAL with Scale and Precision

When a NUMERIC or DECIMAL data type declaration includes both precision and scale, values containing a fractional portion can be stored, and you can control the number of fractional digits. InterBase stores such values internally as SMALLINT, INTEGER,or 64-bit integer data, depending on the precision specified. How can a number with a fractional portion be stored as an integer value? For all SMALLINT and INTEGER data entered, InterBase stores a scale factor, a negative number indicating how many decimal places are contained in the number, based on the power of 10. A scale factor of –1 indicates a fractional portion of tenths; a –2 scale factor indicates a fractional portion of hundredths. You do not need to include the sign; it is negative by default.

For example, when you specify NUMERIC(4,2), InterBase stores the number internally as a SMALLINT. If you insert the number 25.253, it is stored as a decimal 25.25, with 4 digits of precision, and a scale of 2.

The number is divided by 10 to the power of <scale> (number/10 <scale>) to produce a number without a fractional portion.

See the Language Reference Guidefor information about arithmetic operations using exact and approximate numerics.

Numeric Input and Exponents

Any numeric string in DSQL or isql that can be stored as a DECIMAL(18,S) is evaluated exactly, without the loss of precision that might result from intermediate storage as a DOUBLE. A numeric string is recognized by the DSQL parser as a floating-point value only if it contains an “e” or “E” followed by an exponent, which may be zero. For example, DSQL recognizes 4.21 as a scaled exact integer, and passes it to the engine in that form. On the other hand, DSQL recognizes 4.21E0 as a floating-point value.

Specifying Data Types Using Embedded Applications

DSQL applications such as isql can correct for the scale factor for SMALLINT and INTEGER data types by examining the XSQLVAR sqlscale field and dividing to produce the correct value.

Important: Embedded applications cannot use or recognize small precision NUMERIC or DECIMAL data types with fractional portions when they are stored as ­SMALLINT or INTEGER types. To avoid this problem, create all NUMERIC and DECIMAL data types that are to be accessed from embedded applications with a precision of 10 or more, which forces them to be stored as 64-bit integer types. Remember to specify a scale if you want to control the precision and scale.

Both SQL and DSQL applications handle NUMERIC and DECIMAL types stored as 64-bit integer without problem.

Considering Migration for NUMERIC and DECIMAL Data Types

NUMERIC and DECIMAL data types that have a precision greater than 9 are stored differently in dialect 1 and dialect 3 databases. As you migrate your databases to dialect 3, consider the following questions about columns defined with NUMERIC and DECIMAL data types:

• Is the precision less than 10? There is no issue. You can migrate without taking any action and there will be no change in the database and no effect on clients.
• For NUMERIC and DECIMAL columns with precision equal to or greater than 10, is DOUBLE PRECISION an appropriate way to store your data?

• In many cases, the answer is “yes.” If you want to continue to store your data as DOUBLE PRECISION, change the audiotape of the column to DOUBLE PRECISION either before or after migrating your database to dialect 3. This doesn’t change any functionality in dialect 3, but it brings the declaration into line with the storage mode. In a dialect 3 database, newly-created columns of this type are stored as INT64, but migrated columns are still stored as DOUBLE PRECISION. Changing the declaration avoids confusion.
• DOUBLE PRECISION might not be appropriate or desirable for financial applications and others that are sensitive to rounding errors. In this case, you need to take steps to migrate your column so that it is stored as INT64 in dialect 3. As you make this decision, remember that INT64 does not store the same range as DOUBLE PRECISION. Check whether you will lose information in this conversion and whether this is acceptable.

Migrating Databases with NUMERIC and DECIMAL Data Types

Read the “considering migration” section above to decide whether you have columns in a dialect 1 database that would be best stored as 64-bit INT values in a dialect 3 database. If this is the case, follow these steps for each column:

1. Back up your original database. Read the “migration” appendix in the Operations Guide to determine what preparations you need to make before migrating the database. Typically, this includes detecting metadata that uses double quotes around strings. After making necessary preparations as indicated in the migration chapter, back up the database using its current gbak version and restore it using the latest InterBase.
2. Use gfix -set_db_SQL_dialect 3 to change the database to dialect 3.
3. Use the ALTER COLUMN clause of the ALTER DATABASE statement to change the name of each affected column to something different from its original name. If column position is going to be an issue with any of your clients, use ALTER COLUMN to change the positions as well.
4. Create a new column for each one that you are migrating. Use the original column names and if necessary, positions. Declare each one as a DECIMAL or NUMERIC with precision greater than 9.
5. Use UPDATE to copy the data from each old column to its corresponding new column:

   UPDATE tablename
   SET new_col_name = old_col_name;
   

6. Check that your data has been successfully copied to the new columns and drop the old columns.

Using Exact Numeric Data Types in Arithmetic

In SQL dialect 1, when you divide two integers or two DECIMAL(9,2) values, the quotient has type DOUBLE PRECISION; in other words, it is a floating-point value.

In SQL dialect 3, the quotient of two exact numeric values (SMALLINT, INTEGER, NUMERIC(n,m) or DECIMAL(n.m)) is an exact numeric, with scale factor equal to the sum of the scales of the dividend and divisor. Because a SMALLINT or INTEGER has a scale of 0, the quotient of two INTEGERs is an INTEGER, the quotient of a DECIMAL(9,2) and a DECIMAL(12,3) is a DECIMAL(18,5).

In dialect 1, the fraction 1/3 is 0.33333333333333e0; in dialect 3 it is 0. When an application does something that causes a CHECK condition to be checked, or a stored procedure to be executed, or a trigger to fire, the processing that takes place is based on the dialect under which the check, stored procedure, or trigger was defined, not the dialect in effect when the application causes the check, stored procedure, or trigger to be executed.

For example, suppose that a database is migrated from InterBase 5 and thus has dialect 1; that MYCOL1 and MYCOL2 are SQL INTEGERs; and that a table definition includes the following:

CHECK(MYCOL1/MYCOL2>0.5)

which was defined using client dialect 1.

Now suppose that a dialect 3 client tries to insert a row in which MYCOL1 is 3 and MYCOL2 is 5; because the CHECK was defined in dialect 1, the quotient will be 0.600000000000e0 and the row will pass the check condition, even though in the current client’s dialect 3, the quotient would have been the integer 0 and the row would have failed the check, so the insertion would have been refused.

Advance To:

• Floating-point Data Types