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.
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.
Precision | Data type | Dialect 1 | Dialect 3 |
---|---|---|---|
1 - 4 |
|
|
|
1 - 4 |
|
|
|
5 - 9 |
|
|
|
10 - 18 |
|
|
|
3.3.1. NUMERIC
Data Declaration Format
NUMERIC
| NUMERIC(precision)
| NUMERIC(precision, scale)
Parameter | Description |
---|---|
precision | Precision, between 1 and 18. Defaults to 9. |
scale | Scale, between 0 and scale. Defaults to 0. |
Storage Examples
Further 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)
Always keep in mind that the storage format depends on the precision. For instance, you define the column type as |
3.3.2. DECIMAL
Data Declaration Format
DECIMAL
| DECIMAL(precision)
| DECIMAL(precision, scale)
Parameter | Description |
---|---|
precision | Precision, between 1 and 18. Defaults to 9. |
scale | Scale, between 0 and scale. Defaults to 0. |
Storage Examples
The 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)