## 3.3. Fixed-Point Data Types

Fixed-point data types ensure the predictability of multiplication and division operations, making them the choice for storing monetary values. Firebird implements two fixed-point data types: `NUMERIC` and `DECIMAL`. According to the standard, both types limit the stored number to the declared scale (the number of digits after the decimal point).

Different treatments limit precision for each type: precision for `NUMERIC` columns is exactly as declared, while `DECIMAL` columns accepts numbers whose precision is at least equal to what was declared.

Note

The behaviour of `NUMERIC` and `DECIMAL` in Firebird is like the SQL-standard `DECIMAL`; the precision is at least equal to what was declared.

For instance, `NUMERIC(4, 2)` defines a number consisting altogether of four digits, including two digits after the decimal point; that is, it can have up to two digits before the point and no more than two digits after the point. If the number 3.1415 is written to a column with this data type definition, the value of 3.14 will be saved in the `NUMERIC(4, 2)` column.

The form of declaration for fixed-point data, for instance, `NUMERIC(p, s)`, is common to both types. It is important to realise that the `s` argument in this template is scale, rather than a count of digits after the decimal point. Understanding the mechanism for storing and retrieving fixed-point data should help to visualise why: for storage, the number is multiplied by 10s (10 to the power of `s`), converting it to an integer; when read, the integer is converted back.

The method of storing fixed-point data in the DBMS depends on several factors: declared precision, database dialect, declaration type.

Table 3.2Method of Physical Storage for Real Numbers
PrecisionData typeDialect 1Dialect 3

1 - 4

`NUMERIC`

`SMALLINT`

`SMALLINT`

1 - 4

`DECIMAL`

`INTEGER`

`INTEGER`

5 - 9

`NUMERIC` or `DECIMAL`

`INTEGER`

`INTEGER`

10 - 18

`NUMERIC` or `DECIMAL`

`DOUBLE PRECISION`

`BIGINT`

### 3.3.1. `NUMERIC`

Data Declaration Format

```  |`  NUMERIC`
|`| NUMERIC(precision)`
|`| NUMERIC(precision, scale)`
```

Table 3.3`NUMERIC` Type Parameters
ParameterDescription

precision

Precision, between 1 and 18. Defaults to 9.

scale

Scale, between 0 and scale. Defaults to 0.

Storage ExamplesFurther to the explanation above, the DBMS will store `NUMERIC` data according the declared precision and scale. Some more examples are:

```  |`NUMERIC(4) stored as      SMALLINT (exact data)`
|`NUMERIC(4,2)              SMALLINT (data * 102)`
|`NUMERIC(10,4) (Dialect 1) DOUBLE PRECISION`
|`              (Dialect 3) BIGINT (data * 104)`
```
Caution

Always keep in mind that the storage format depends on the precision. For instance, you define the column type as `NUMERIC(2,2)` presuming that its range of values will be -0.99…​0.99. However, the actual range of values for the column will be -327.68..327.67, which is due to storing the `NUMERIC(2,2)` data type in the `SMALLINT` format. In storage, the `NUMERIC(4,2)`, `NUMERIC(3,2)` and `NUMERIC(2,2)` data types are the same, in fact. It means that if you really want to store data in a column with the `NUMERIC(2,2)` data type and limit the range to -0.99…​0.99, you will have to create a constraint for it.

### 3.3.2. `DECIMAL`

Data Declaration Format

```  |`  DECIMAL`
|`| DECIMAL(precision)`
|`| DECIMAL(precision, scale)`
```

Table 3.4`DECIMAL` Type Parameters
ParameterDescription

precision

Precision, between 1 and 18. Defaults to 9.

scale

Scale, between 0 and scale. Defaults to 0.

Storage ExamplesThe storage format in the database for `DECIMAL` is very similar to `NUMERIC`, with some differences that are easier to observe with the help of some more examples:

```  |`DECIMAL(4) stored as      INTEGER (exact data)`
|`DECIMAL(4,2)              INTEGER (data * 102)`
|`DECIMAL(10,4) (Dialect 1) DOUBLE PRECISION`
|`              (Dialect 3) BIGINT (data * 104)`
```