# 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.