apache > db
Apache DB Project
 
Font size:      

Numeric Types

Numeric Type Overview

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

Numeric Type Promotion in Expressions

In expressions that use only integer types, Derby promotes the type of the result to at least INTEGER. In expressions that mix integer with non-integer types, Derby promotes the result of the expression to the highest type in the expression. Table 4 shows the promotion of data types in expressions.

Table 4. Type Promotion in Expressions

Largest Type That Appears in ExpressionResulting Type of Expression
DOUBLE PRECISIONDOUBLE PRECISION
REALDOUBLE PRECISION
DECIMALDECIMAL
BIGINTBIGINT
INTEGERINTEGER
SMALLINTINTEGER

For example:

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

Storing Values of One Numeric Data Type in Columns of Another Numeric Data Type

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

Note:
When truncating trailing digits from a NUMERIC value, Derby rounds down.

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:

  • 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 = 27 

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

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


Previous Page
Next Page
Table of Contents
Index