Arithmetic Operations and Type Casting of Numeric Data Types

Description

All numeric data types can be used for arithmetic operations. The result type of the operation differs depending on the data types of the operands and the type of the operation. The following table shows the result data types of addition/subtraction/multiplication for each operand type.

Result Data Type by Operand Type

 

INT

NUMERIC

FLOAT

DOUBLE

MONETARY

INT

INT
(BIGINT)

NUMERIC

FLOAT

DOUBLE

MONETARY

NUMERIC

NUMERIC

NUMERIC
(p and s are also converted)

DOUBLE

DOUBLE

MONETARY

FLOAT

FLOAT

DOUBLE

FLOAT

DOUBLE

MONETARY

DOUBLE

DOUBLE

DOUBLE

DOUBLE

DOUBLE

MONETARY

MONETARY

MONETARY

MONETARY

MONETARY

MONETARY

MONETARY

Note that the result type of the operation does not change if all operands are of the same data type but type casting occurs exceptionally in division operations. An error occurs when a denominator, i.e. a divisor, is 0.

If one of the operands is a MONETARY type, all operation results are cast to MONETARY type because a MONETARY type uses the same operation methods as the DOUBLE type.

The following table shows the total number of digits (p) and the number of digits after the decimal point (s) of the operation results when all operands are of the NUMERIC type. 

Result of NUMERIC Type Operation

Operation

Maximum Precision

Maximum Scale

N(p1, s1) + N(p2, s2)

max(p1-s1, p2-s2)+max(s1, s2) +1

max(s1, s2)

N(p1, s1) - N(p2, s2)

max(p1-s1, p2-s2)+max(s1, s2)

max(s1, s2)

N(p1, s1) * N(p2, s2)

p1+p2+1

s1+s2

N(p1, s1) / N(p2, s2)

Let Pt = p1+max(s1, s2) + s2 - s1 when s2 > 0 and Pt = p1 in other cases; St = s1 when s1 > s2 and s2 in other cases; the number of decimal places is min(9-St, 38-Pt) + St when St < 9 and St in other cases.

Example

--int * int

SELECT 123*123;

      123*123

=============

        15129

 

-- int * int returns overflow error

SELECT (1234567890123*1234567890123);

 

ERROR: Data overflow on data type bigint.

 

-- int * numeric returns numeric type  

SELECT (1234567890123*CAST(1234567890123 AS NUMERIC(15,2)));

 (1234567890123* cast(1234567890123 as numeric(15,2)))

======================

  1524157875322755800955129.00

 

-- int * float returns float type

SELECT (1234567890123*CAST(1234567890123 AS FLOAT));

 (1234567890123* cast(1234567890123 as float))

===============================================

                                  1.524158e+024

 

-- int * double returns double type

SELECT (1234567890123*CAST(1234567890123 AS DOUBLE));

 (1234567890123* cast(1234567890123 as double))

================================================

                          1.524157875322756e+024

 

-- numeric * numeric returns numeric type   

SELECT (CAST(1234567890123 AS NUMERIC(15,2))*CAST(1234567890123 AS NUMERIC(15,2)));

 ( cast(1234567890123 as numeric(15,2))* cast(1234567890123 as numeric(15,2)))

======================

  1524157875322755800955129.0000

 

-- numeric * float returns double type  

SELECT (CAST(1234567890123 AS NUMERIC(15,2))*CAST(1234567890123 AS FLOAT));

 ( cast(1234567890123 as numeric(15,2))* cast(1234567890123 as float))

=======================================================================

                                                 1.524157954716582e+024

 

-- numeric * double returns double type  

SELECT (CAST(1234567890123 AS NUMERIC(15,2))*CAST(1234567890123 AS DOUBLE));

 ( cast(1234567890123 as numeric(15,2))* cast(1234567890123 as double))

========================================================================

                                                  1.524157875322756e+024

 

-- float * float returns float type  

SELECT (CAST(1234567890123 AS FLOAT)*CAST(1234567890123 AS FLOAT));

 ( cast(1234567890123 as float)* cast(1234567890123 as float))

===============================================================

                                                  1.524158e+024

-- float * double returns float type  

SELECT (CAST(1234567890123 AS FLOAT)*CAST(1234567890123 AS DOUBLE));

 ( cast(1234567890123 as float)* cast(1234567890123 as double))

================================================================

                                          1.524157954716582e+024

 

-- double * double returns float type  

SELECT (CAST(1234567890123 AS DOUBLE)*CAST(1234567890123 AS DOUBLE));

 ( cast(1234567890123 as double)* cast(1234567890123 as double))

=================================================================

                                           1.524157875322756e+024

 

-- int / int returns int type without type conversion or rounding

SELECT 100100/100000;

  100100/100000

===============

              1

 

-- int / int returns int type without type conversion or rounding

SELECT 100100/200200;

  100100/200200

===============

              0

 

-- int / zero returns error

SELECT 100100/(100100-100100);

ERROR: Attempt to divide by zero.