Fixeddecimal Data Types
Go Up to Defining Numeric Data Types
Contents
 1 NUMERIC data type
 2 DECIMAL data type
 3 How fixeddecimal Data Types are Stored
 4 Specifying NUMERIC and DECIMAL with Scale and Precision
 5 Numeric Input and Exponents
 6 Specifying Data Types Using Embedded Applications
 7 Considering Migration for NUMERIC and DECIMAL Data Types
 8 Migrating Databases with NUMERIC and DECIMAL Data Types
 9 Using Exact Numeric Data Types in Arithmetic
 10 Advance To:
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 fixeddecimal 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
andDECIMAL
data types that are declared without either precision or scale are stored asINTEGER
. Defined with precision, with or without scale, they are stored as
SMALLINT
,INTEGER, DOUBLE PRECISION
or 64bit integer. Storage type depends on both the precision and the dialect of the database.
Precision  Dialect 1  Dialect 3 

1 to 4 


5 to 9 


10 to 18 


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:
Data type specified as…  Data type stored as… 





























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 64bit 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 floatingpoint 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 floatingpoint 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
orDECIMAL
data types with fractional portions when they are stored asSMALLINT
orINTEGER
types. To avoid this problem, create allNUMERIC
andDECIMAL
data types that are to be accessed from embedded applications with a precision of 10 or more, which forces them to be stored as 64bit 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 64bit 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
andDECIMAL
columns with precision equal to or greater than 10, isDOUBLE 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 toDOUBLE 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, newlycreated columns of this type are stored asINT64
, but migrated columns are still stored asDOUBLE 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 asINT64
in dialect 3. As you make this decision, remember thatINT64
does not store the same range asDOUBLE PRECISION
. Check whether you will lose information in this conversion and whether this is acceptable.
 In many cases, the answer is “yes.” If you want to continue to store your data as
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 64bit INT
values in a dialect 3 database. If this is the case, follow these steps for each column:
 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.  Use
gfix set_db_SQL_dialect 3
to change the database to dialect 3.  Use the
ALTER COLUMN
clause of theALTER 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, useALTER COLUMN
to change the positions as well.  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
orNUMERIC
with precision greater than 9.  Use
UPDATE
to copy the data from each old column to its corresponding new column:UPDATE tablename SET new_col_name = old_col_name;
 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 floatingpoint 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.