FirebirdSQL logo
 SQL Language StructureCommon Language Elements 
DECFLOAT and Functions
Use with Standard Functions

A number of standard scalar functions can be used with expressions and values of the DECFLOAT type.They are:

ABS

CEILING

EXP

FLOOR

LN

LOG

LOG10

POWER

SIGN

SQRT

The aggregate functions SUM, AVG, MAX and MIN work with DECFLOAT data, as do all the statistical aggregates (including but not limited to STDDEV or CORR).

Special Functions for DECFLOAT

Firebird supports four functions, designed to support DECFLOAT data specifically:

COMPARE_DECFLOAT

compares two DECFLOAT values to be equal, different or unordered

NORMALIZE_DECFLOAT

takes a single DECFLOAT argument and returns it in its simplest form

QUANTIZE

takes two DECFLOAT arguments and returns the first argument scaled using the second value as a pattern

TOTALORDER

performs an exact comparison on two DECFLOAT values

Detailed descriptions are available in the Special Functions for DECFLOAT section of the Built-in Scalar Functions chapter.

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

Note

The behaviour of both 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[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 fixed-point 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 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 by multiplying by 10-s (or, dividing by 10s).

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

Table 1. Method of Physical Storage for Fixed-Point Numbers
Precision Data type Dialect 1 Dialect 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

19 - 38

NUMERIC or DECIMAL

INT128

INT128

Note

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


1. in practice, the actual range is determined by the backing type, for NUMERIC(4, s) that is SMALLINT, which means it can store [-327.68, 327.67

NUMERIC

Data Type Declaration Format
NUMERIC [(precision [, scale])]
Table 1. NUMERIC Type Parameters
Parameter Description

precision

Precision, between 1 and 38.Defaults to 9.

scale

Scale, between 0 and precision.Defaults to 0.

Storage Examples

Further 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 * 102)
NUMERIC(10,4) (Dialect 1) DOUBLE PRECISION
              (Dialect 3) BIGINT (data * 104)
NUMERIC(38,6)             INT128 (data * 106)
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.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.

DECIMAL

Data Type Declaration Format
{ DECIMAL | DEC } [(precision [, scale])]
Table 1. DECIMAL Type Parameters
Parameter Description

precision

Precision, between 1 and 38.Defaults to 9.

scale

Scale, between 0 and precision.Defaults to 0.

Storage Examples

The 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 * 102)
DECIMAL(10,4) (Dialect 1) DOUBLE PRECISION
              (Dialect 3) BIGINT (data * 104)
DECIMAL(38,6)             INT128 (data * 106)