# Numeric types
Numeric data types include integers, decimals, and floating-point numbers.
**Topics**
+ [Integer types](Numeric_types-integer-types.md)
+ [DECIMAL or NUMERIC type](Numeric_types-decimal-or-numeric-type.md)
+ [Floating-point types](Numeric_types-floating-point-types.md)
+ [Computations with numeric values](Numeric_computations.md)
# Integer types
Use the following data types to store whole numbers of various ranges. You can't store values outside of the allowed range for each type.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_types-integer-types.html)
# DECIMAL or NUMERIC type
Use the DECIMAL or NUMERIC data type to store values with a *user-defined precision*. The DECIMAL and NUMERIC keywords are interchangeable. In this document, *decimal* is the preferred term for this data type. The term *numeric* is used generically to refer to integer, decimal, and floating-point data types.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_types-decimal-or-numeric-type.html)
Define a DECIMAL column in a table by specifying a *precision* and *scale*:
```
decimal(precision, scale)
```
*precision*
The total number of significant digits in the whole value: the number of digits on both sides of the decimal point. For example, the number `48.2891` has a precision of 6 and a scale of 4. The default precision, if not specified, is 18. The maximum precision is 38.
If the number of digits to the left of the decimal point in an input value exceeds the precision of the column minus its scale, the value can't be copied into the column (or inserted or updated). This rule applies to any value that falls outside the range of the column definition. For example, the allowed range of values for a `numeric(5,2)` column is `-999.99` to `999.99`.
*scale*
The number of decimal digits in the fractional part of the value, to the right of the decimal point. Integers have a scale of zero. In a column specification, the scale value must be less than or equal to the precision value. The default scale, if not specified, is 0. The maximum scale is 37.
If the scale of an input value that is loaded into a table is greater than the scale of the column, the value is rounded to the specified scale. For example, the PRICEPAID column in the SALES table is a DECIMAL(8,2) column. If a DECIMAL(8,4) value is inserted into the PRICEPAID column, the value is rounded to a scale of 2.
```
insert into sales
values (0, 8, 1, 1, 2000, 14, 5, 4323.8951, 11.00, null);
select pricepaid, salesid from sales where salesid=0;
pricepaid | salesid
-----------+---------
4323.90 | 0
(1 row)
```
However, results of explicit casts of values selected from tables are not rounded.
**Note**
The maximum positive value that you can insert into a DECIMAL(19,0) column is `9223372036854775807` (263 -1). The maximum negative value is `-9223372036854775807`. For example, an attempt to insert the value `9999999999999999999` (19 nines) will cause an overflow error. Regardless of the placement of the decimal point, the largest string that AWS Clean Rooms can represent as a DECIMAL number is `9223372036854775807`. For example, the largest value that you can load into a DECIMAL(19,18) column is `9.223372036854775807`.
These rules are because of the following:
DECIMAL values with 19 or fewer significant digits of precision are stored internally as 8-byte integers.
DECIMAL values with 20 to 38 significant digits of precision are stored as 16-byte integers.
## Notes about using 128-bit DECIMAL or NUMERIC columns
Do not arbitrarily assign maximum precision to DECIMAL columns unless you are certain that your application requires that precision. 128-bit values use twice as much disk space as 64-bit values and can slow down query execution time.
# Floating-point types
Use the REAL and DOUBLE PRECISION data types to store numeric values with *variable precision*. These types are *inexact* types, meaning that some values are stored as approximations, such that storing and returning a specific value may result in slight discrepancies. If you require exact storage and calculations (such as for monetary amounts), use the DECIMAL data type.
REAL represents the single-precision floating point format, according to the IEEE Standard 754 for Floating-Point Arithmetic. It has a precision of about 6 digits, and a range of around 1E-37 to 1E\$137. You can also specify this data type as FLOAT4.
DOUBLE PRECISION represents the double-precision floating point format, according to the IEEE Standard 754 for Binary Floating-Point Arithmetic. It has a precision of about 15 digits, and a range of around 1E-307 to 1E\$1308. You can also specify this data type as FLOAT or FLOAT8.
# Computations with numeric values
In AWS Clean Rooms, *computation* refers to binary mathematical operations: addition, subtraction, multiplication, and division. This section describes the expected return types for these operations, as well as the specific formula that is applied to determine precision and scale when DECIMAL data types are involved.
When numeric values are computed during query processing, you might encounter cases where the computation is impossible and the query returns a numeric overflow error. You might also encounter cases where the scale of computed values varies or is unexpected. For some operations, you can use explicit casting (type promotion) or AWS Clean Rooms configuration parameters to work around these problems.
For information about the results of similar computations with SQL functions, see [AWS Clean Rooms Spark SQL functions](sql-functions-topic-spark.md).
## Return types for computations
Given the set of numeric data types supported in AWS Clean Rooms, the following table shows the expected return types for addition, subtraction, multiplication, and division operations. The first column on the left side of the table represents the first operand in the calculation, and the top row represents the second operand.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_computations.html)
## Precision and scale of computed DECIMAL results
The following table summarizes the rules for computing resulting precision and scale when mathematical operations return DECIMAL results. In this table, `p1` and `s1` represent the precision and scale of the first operand in a calculation. `p2` and `s2` represent the precision and scale of the second operand. (Regardless of these calculations, the maximum result precision is 38, and the maximum result scale is 38.)
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_computations.html)
For example, the PRICEPAID and COMMISSION columns in the SALES table are both DECIMAL(8,2) columns. If you divide PRICEPAID by COMMISSION (or vice versa), the formula is applied as follows:
```
Precision = 8-2 + 2 + max(4,2+8-2+1)
= 6 + 2 + 9 = 17
Scale = max(4,2+8-2+1) = 9
Result = DECIMAL(17,9)
```
The following calculation is the general rule for computing the resulting precision and scale for operations performed on DECIMAL values with set operators such as UNION, INTERSECT, and EXCEPT or functions such as COALESCE and DECODE:
```
Scale = max(s1,s2)
Precision = min(max(p1-s1,p2-s2)+scale,19)
```
For example, a DEC1 table with one DECIMAL(7,2) column is joined with a DEC2 table with one DECIMAL(15,3) column to create a DEC3 table. The schema of DEC3 shows that the column becomes a NUMERIC(15,3) column.
```
select * from dec1 union select * from dec2;
```
In the above example, the formula is applied as follows:
```
Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15
Scale = max(2,3) = 3
Result = DECIMAL(15,3)
```
## Notes on division operations
For division operations, divide-by-zero conditions return errors.
The scale limit of 100 is applied after the precision and scale are calculated. If the calculated result scale is greater than 100, division results are scaled as follows:
+ Precision = ` precision - (scale - max_scale)`
+ Scale = ` max_scale `
If the calculated precision is greater than the maximum precision (38), the precision is reduced to 38, and the scale becomes the result of: `max(38 + scale - precision), min(4, 100))`
## Overflow conditions
Overflow is checked for all numeric computations. DECIMAL data with a precision of 19 or less is stored as 64-bit integers. DECIMAL data with a precision that is greater than 19 is stored as 128-bit integers. The maximum precision for all DECIMAL values is 38, and the maximum scale is 37. Overflow errors occur when a value exceeds these limits, which apply to both intermediate and final result sets:
+ Explicit casting results in runtime overflow errors when specific data values don't fit the requested precision or scale specified by the cast function. For example, you can't cast all values from the PRICEPAID column in the SALES table (a DECIMAL(8,2) column) and return a DECIMAL(7,3) result:
```
select pricepaid::decimal(7,3) from sales;
ERROR: Numeric data overflow (result precision)
```
This error occurs because *some* of the larger values in the PRICEPAID column can't be cast.
+ Multiplication operations produce results in which the result scale is the sum of the scale of each operand. If both operands have a scale of 4, for example, the result scale is 8, leaving only 10 digits for the left side of the decimal point. Therefore, it is relatively easy to run into overflow conditions when multiplying two large numbers that both have significant scale.
## Numeric calculations with INTEGER and DECIMAL types
When one of the operands in a calculation has an INTEGER data type and the other operand is DECIMAL, the INTEGER operand is implicitly cast as a DECIMAL.
+ SMALLINT or SHORT is cast as DECIMAL(5,0)
+ INTEGER is cast as DECIMAL(10,0)
+ BIGINT or LONG is cast as DECIMAL(19,0)
For example, if you multiply SALES.COMMISSION, a DECIMAL(8,2) column, and SALES.QTYSOLD, a SMALLINT column, this calculation is cast as:
```
DECIMAL(8,2) * DECIMAL(5,0)
```