Numeric types
Topics
Numeric data types include integers, decimals, and floatingpoint numbers.
Integer types
Use the SMALLINT, INTEGER, and BIGINT data types to store whole numbers of various ranges. You cannot store values outside of the allowed range for each type.
Name  Storage  Range 

SMALLINT or INT2  2 bytes  32768 to +32767 
INTEGER, INT, or INT4  4 bytes  2147483648 to +2147483647 
BIGINT or INT8  8 bytes  9223372036854775808 to 9223372036854775807 
DECIMAL or NUMERIC type
Use the DECIMAL or NUMERIC data type to store values with a userdefined 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 floatingpoint data types.
Storage  Range 

Variable, up to 128 bits for uncompressed DECIMAL types.  128bit signed integers with up to 38 digits of precision. 
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 cannot 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 is999.99
to999.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
(2^{63}
1). The maximum negative value is 9223372036854775808
. 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 Amazon Redshift 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 DECIMAL values with 19 or fewer significant digits of precision are stored internally as 8byte integers, while DECIMAL values with 20 to 38 significant digits of precision are stored as 16byte integers.
Notes about using 128bit DECIMAL or NUMERIC columns
Do not arbitrarily assign maximum precision to DECIMAL columns unless you are certain that your application requires that precision. 128bit values use twice as much disk space as 64bit values and can slow down query execution time.
FloatingPoint 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 singleprecision floating point format, according to the IEEE Standard 754 for Binary FloatingPoint Arithmetic. It has a precision of about 6 digits, and a range of around 1E37 to 1E+37. You can also specify this data type as FLOAT4.
DOUBLE PRECISION represents the doubleprecision floating point format, according to the IEEE Standard 754 for Binary FloatingPoint Arithmetic. It has a precision of about 15 digits, and a range of around 1E307 to 1E+308. You can also specify this data type as FLOAT or FLOAT8.
In addition to ordinary numeric values, the floatingpoint types have several special values. Use single quotation marks around these values when using them in SQL:
NaN
– notanumberInfinity
– infinityInfinity
– negative infinity
For example, to insert notanumber in column day_charge
of table customer_activity
run the following SQL:
insert into customer_activity(day_charge) values('NaN');