View source for Exact Numerics (Language Reference Guide)

{{Parent|SQL Statement and Function Reference}}

All <source enclose="none" lang="sql">NUMERIC</source> and <source enclose="none" lang="sql">DECIMAL</source> data types are stored as exact numerics: 16, 32, or 64 bits, depending on the precision. <source enclose="none" lang="sql">NUMERIC</source> and <source enclose="none" lang="sql">DECIMAL</source> data types with precision greater than 9 are referred to as large exact numerics.

* If one operand is an approximate numeric, the result of any dyadic operation (addition, subtraction, multiplication, division) is DOUBLE PRECISION.
* Any value that can be stored in a DECIMAL(18,S) can also be specified as the default value for a column or a domain.


==Addition and Subtraction==
If both operands are exact numeric, adding or subtracting the operands produces an exact numeric with a precision of 18 and a scale equal to the larger of the two. For example:

<pre>
CREATE TABLE t1 (n1 NUMERIC(16,2), n2 NUMERIC(16,3));
INSERT INTO t1 VALUES (12.12, 123.123);
COMMIT;
</pre>

The following query returns the integer 135.243. The largest scale of the two operands is 3; therefore, the scale of the sum is 3.

<pre>
SELECT n1 + n2 FROM t1;
</pre>

Similarly, the following query returns the integer -111.003:

<pre>
SELECT n1 - n2 FROM t1;
</pre>

If either of the operands is approximate numeric (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.


==Multiplication==
If both operands are exact numeric, multiplying the operands produces an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the operands. For example:

<pre>
CREATE TABLE t1 (n1 NUMERIC(16,2), n2 NUMERIC(16,3));
INSERT INTO t1 VALUES (12.12, 123.123);
COMMIT;
</pre>

the following query returns the integer 1492.25076 because n1 has a scale of 2 and n2 has a scale of 3. the sum of the scales is 5.

<pre>
SELECT n1*n2 FROM t1
</pre>

If one of the operands is approximate numeric (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.


==Division==


If both operands are exact numeric, dividing the operands produces an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the operands. If at least one operand of a division operator has an approximate numeric type (FLOAT, REAL, or DOUBLE PRECISION), the result is DOUBLE PRECISION.

For example, in the following table, division operations produce a variety of results:

<pre>
CREATE TABLE t1 (i1 INTEGER), i2 INTEGER, n1 NUMERIC(16,2)
n2 NUMERIC(16,2));
INSERT INTO t1 VALUES (1, 3, 1.00, 3.00);
COMMIT;
</pre>

The following query returns the integer 0 because each operand has a scale of 0, so the sum of the scales is 0:

<pre>
SELECT i1/i2 FROM t1
</pre>

The following query returns the NUMERIC(18,2) value 0.33, because the sum of the scales 0 (operand 1) and 2 (operand 2) is 2:

<pre>
SELECT i1/n2 FROM t1
</pre>

The following query returns the NUMERIC(18,4) value 0.3333, because the sum of the two operand scales is 4:

<pre>
SELECT n1/n2 FROM t1
</pre>

In {{Product}} 5 and earlier, any of the above division operations would have returned the DOUBLE PRECISION value 0.3333333333333333.

== Advance To: ==
* [[Error Handling (Language Reference Guide)|Error Handling]]
[[Category: Language Reference Guide]]