3.3. FixedPoint Data Types
Fixedpoint data types ensure the predictability of multiplication and division operations, making them the choice for storing monetary values.
Firebird implements two fixedpoint data types: NUMERIC
and DECIMAL
.
According to the SQL standard, both types limit the stored number to the declared scale (the number of digits after the decimal point).
The standard defines different treatment of the 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 both NUMERIC
and DECIMAL
in Firebird is like the SQLstandard 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^{1}] 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 fixedpoint data, for instance, NUMERIC(p, s)
, is common to both types.
The s
argument in this template is scale.
Understanding the mechanism for storing and retrieving fixedpoint data should help to visualise why: for storage, the number is multiplied by 10^{s} (10 to the power of s
), converting it to an integer;
when read, the integer is converted back by multiplying by 10^{s} (or, dividing by 10^{s}).
The method of storing fixedpoint data in the database 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 



19  38 



Numerics with precision less than 19 digits use SMALLINT
, INTEGER
, BIGINT
or DOUBLE PRECISION
as the base data type, depending on the number of digits and SQL dialect.
When precision is between 19 and 38 digits an INT128
is used as the base data type, and the actual precision is always extended to the full 38 digits.
For complex calculations, those digits are cast internally to DECFLOAT(34).
The result of various mathematical operations, such as LOG()
, EXP()
and so on, and aggregate functions using a high precision numeric argument, will be DECFLOAT(34)
.
3.3.1. NUMERIC
Data Type Declaration Format
NUMERIC [(precision [, scale])]
NUMERIC
Type ParametersParameter  Description 

precision  Precision, between 1 and 38. Defaults to 9. 
scale  Scale, between 0 and precision. Defaults to 0. 
Storage ExamplesFurther to the explanation above, Firebird 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 * 10^{2})
NUMERIC(10,4) (Dialect 1) DOUBLE PRECISION
(Dialect 3) BIGINT (data * 10^{4})
NUMERIC(38,6) INT128 (data * 10^{6})
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.
This means that if you need 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 CHECK
constraint for it.
3.3.2. DECIMAL
Data Type Declaration Format
{ DECIMAL  DEC } [(precision [, scale])]
DECIMAL
Type ParametersParameter  Description 

precision  Precision, between 1 and 38. Defaults to 9. 
scale  Scale, between 0 and precision. Defaults to 0. 
Storage ExamplesThe storage format in the database for DECIMAL
is 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 * 10^{2})
DECIMAL(10,4) (Dialect 1) DOUBLE PRECISION
(Dialect 3) BIGINT (data * 10^{4})
DECIMAL(38,6) INT128 (data * 10^{6})