Numeric types

Name Alias Range Description

BIGINT

INTEGER

…​

Integers

NUMERIC

DECIMAL.

WIDTH: 37

Real numbers with a given precision

DOUBLE

FLOAT

…​

Real numbers with variable precision

Integer type

  • BIGINT.

    • Alias: INTEGER

The BIGINT data type stores integers, that is, numbers without fractional components, of various ranges. Attempts to store values outside the valid range will result in an error.

The range of possible values for the BIGINT type is from to .

Type with specified precision

  • NUMERIC.

    • Alias: DECIMAL

The NUMERIC (WIDTH, SCALE) data type represents an exact decimal value with a fixed decimal separator — a dot.

When creating a value of type NUMERIC, you can specify the parameters WIDTH and SCALE.

  • The WIDTH parameter defines the maximum total number of digits.

  • The SCALE parameter defines the number of digits after the decimal point.

For example, the NUMERIC(3, 2) type can contain the value , but cannot contain the value or . If the WIDTH and SCALE parameters are not specified explicitly, the default values are used: NUMERIC(37,18).

Adding, subtracting and multiplying two fixed-point decimal numbers returns another fixed-point decimal number with the required WIDTH and SCALE or gives an error if the required WIDTH exceeds the maximum supported WIDTH, which is 37.

If you need to store numbers with a known number of decimal places, as well as precise addition, subtraction and multiplication operations (e.g. for monetary amounts), then you should use the NUMERIC precision data type.

Variable precision type

  • DOUBLE.

    • Alias: FLOAT

The DOUBLE data type is a numeric type with variable precision. This type stores a double precision floating decimal point number (8 bytes).

The range of possible values for the DOUBLE type is from to .

As with the data type NUMERIC, when converting literals or converting other data types to a type with variable precision, input data that cannot be represented exactly is stored as approximate values.

While multiplication, addition, and subtraction for type NUMERIC are exact operations, the same operations for a type with variable precision are only approximate.

If you need to perform fast or complex calculations, a variable-precision data type may be more appropriate than a type of NUMERIC. However, if you use the results of these calculations to make important decisions, you should carefully analyse the implementation of these calculations in borderline cases (ranges, infinite values, anti-overflows, invalid operations, etc.). They may be handled differently than you expect.

Examples

Create a table numbers with columns having numeric types BIGINT, NUMERIC(4,3), DOUBLE and fill one row with values , and :

CREATE TABLE numbers(
    bigint BIGINT,
    numeric NUMERIC(4,3),
    double DOUBLE);

INSERT INTO numbers VALUES
    (2^62, 1.1, 3.14159265358979323846);

SELECT * FROM numbers;
+---------------------+---------+-------------------+
|        bigint       | numeric |       double      |
+---------------------+---------+-------------------+
| 4611686018427388000 | 1.100   | 3.141592653589793 |
+---------------------+---------+-------------------+

Note that in the program output in the numeric column the original number is displayed with the specified precision (3 digits after the decimal separator), and in the double column the original number is displayed with the highest possible precision, so the number of digits after the separator is less than in the entered number.

See also