About Numeric Data Types

This section contains the reference documentation for the numeric data types built into Splice Machine SQL:

Data Type Description
BIGINT

The BIGINT data type provides 8 bytes of storage for integer values.

DECIMAL

The DECIMAL data type provides an exact numeric in which the precision and scale can be arbitrarily sized.

You can use DECIMAL and NUMERIC interchangeably.

DOUBLE

The DOUBLE data type provides 8-byte storage for numbers using IEEE floating-point notation.

DOUBLE PRECISION can be used synonymously with DOUBLE.

DOUBLE PRECISION

The DOUBLE PRECISION data type provides 8-byte storage for numbers using IEEE floating-point notation.

DOUBLE can be used synonymously with DOUBLE PRECISION.

FLOAT

The FLOAT data type is an alias for either a REAL or DOUBLE PRECISION data type, depending on the precision you specify.

INTEGER

INTEGER provides 4 bytes of storage for integer values.

NUMERIC

The NUMERICdata type provides an exact numeric in which the precision and scale can be arbitrarily sized.

You can use NUMERIC and DECIMAL interchangeably.

REAL

The REAL data type provides 4 bytes of storage for numbers using IEEE floating-point notation.

SMALLINT

The SMALLINT data type provides 2 bytes of storage.

Using Numeric Types

Numeric types include the following types, which provide storage of varying sizes:

Numeric Type Data Types
Integer numerics

SMALLINT (2 bytes)

INTEGER (4 bytes)

BIGINT (8 bytes)

Floating-point (also called approximate) numerics

REAL (4 bytes)

DOUBLE PRECISION (8 bytes)

REAL)

Exact numerics

DECIMAL (storage based on precision)

DECIMAL)

Numeric Type Promotion in Expressions

The following table shows the result type of numeric expressions based on the mix of numeric data types in the expressions.

Largest Type That Appears in Expression Resulting Type of Expression
DOUBLE PRECISION DOUBLE PRECISION
REAL DOUBLE PRECISION
DECIMAL DECIMAL
BIGINT BIGINT
INTEGER INTEGER
SMALLINT INTEGER

For example:

   -- returns a double precision value
VALUES 1 + 1.0e0;
   -- returns a decimal value
VALUES 1 + 1.0;
   -- returns an integer value
VALUES CAST (1 AS INT) + CAST (1 AS INT);

Storing Numeric Values

An attempt to put a floating-point type of a larger storage size into a location of a smaller size fails only if the value cannot be stored in the smaller-size location. For example:

create table mytable (r REAL, d DOUBLE PRECISION);
   0 rows inserted/updated/deleted
INSERT INTO mytable (r, d) values (3.4028236E38, 3.4028235E38);
   ERROR X0X41: The number '3.4028236E38' is outside the range for the data type REAL.

You can store a floating point type in an INTEGER column; the fractional part of the number is truncated. For example:

INSERT INTO mytable(integer_column) values (1.09e0);
   1 row inserted/updated/deleted
   SELECT integer_column
   FROM mytable;
---------------
   1

Integer types can always be placed successfully in approximate numeric values, although with the possible loss of some precision.

Integers can be stored in decimals if the DECIMAL precision is large enough for the value. For example:

ij>
insert into mytable (decimal_column) VALUES (55555555556666666666);
   ERROR X0Y21: The number '55555555556666666666' is outside the
   range of the target DECIMAL/NUMERIC(5,2) datatype.

An attempt to put an integer value of a larger storage size into a location of a smaller size fails if the value cannot be stored in the smaller-size location. For example:

INSERT INTO mytable (int_column) values 2147483648;
   ERROR 22003: The resulting value is outside the range for the data type INTEGER.

Splice Machine rounds down when truncating trailing digits from a NUMERIC value.

Scale for Decimal Arithmetic

SQL statements can involve arithmetic expressions that use decimal data types of different precisions (the total number of digits, both to the left and to the right of the decimal point) and scales (the number of digits of the fractional component).

The precision and scale of the resulting decimal type depend on the precision and scale of the operands.

Given an arithmetic expression that involves two decimal operands:

  • lp stands for the precision of the left operand
  • rp stands for the precision of the right operand
  • ls stands for the scale of the left operand
  • rs stands for the scale of the right operand

Use the following formulas to determine the scale of the resulting data type for the following kinds of arithmetical expressions:

Operation Scale
multiplication ls + rs
division 31 - lp + ls - rs
AVG() max(max(ls, rs), 4)
all others max(ls, rs)

For example, the scale of the resulting data type of the following expression is 27:

11.0/1111.33
   // 31 - 3 + 1 - 2 = 2

Use the following formulas to determine the precision of the resulting data type for the following kinds of arithmetical expressions:

Operation Precision
multiplication lp + rp
addition 2 * (p - s) + s
division lp - ls + rp + max(ls + rp - rs + 1, 4)
all others max(lp - ls, rp - rs) + 1 + max(ls, rs)

See Also