Numeric Data Types
There are two categories of numeric data types: exact and approximate. Exact data types include integer data types and decimal data types. Approximate data types include floating point data types.
Integer Data Types
Note: Exact numeric data types include the following integer data types:
• integer1 (one-byte)
• smallint (two-byte)
• integer (four-byte)
• integer8 (eight-byte)
The following table lists the ranges of values for each integer data type:
Integer Data Type | Lowest Possible Value | Highest Possible Value |
|---|---|---|
tinyint (integer1) | -128 | +127 |
smallint (integer2) | -32,768 | +32,767 |
integer (integer4) | -2,147,483,648 | +2,147,483,647 |
bigint (integer8) | -9,223,372,036,854,775,808 | +9,223,372,036,854,775,807 |
Decimal Data Types
The decimal data type is an exact numeric data type defined in terms of its precision (total number of digits) and scale (number of digits to the right of the decimal point).
The following is an example of precision and scale in decimal values:
The minimum precision for a decimal value is 1 and the maximum precision is 39. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).
Note: The decimal data type is suitable for storing currency data where the required range of values or number of digits to the right of the decimal point exceeds the capacities of the money data type. Note that, for display purposes, a currency sign cannot be specified for decimal values.
Decimal Data Type Syntax
Specify the decimal data type using the following syntax:
decimal(p,s)
p
Defines the precision
s
Defines the scale
Note: Valid synonyms for the decimal data type are dec and numeric.
Floating Point Data Types
A floating point value is represented either as whole plus fractional digits (like decimal values) or as a mantissa plus an exponent. The following is an example of the mantissa and exponent parts of floating point values:
There are two floating point data types:
• float4 (4-byte)
• float (8-byte)
Note: A synonym for float4 is real. Synonyms for float are float8 and double precision.
Floating point numbers are stored in four or eight bytes. Internally, eight byte numbers are rounded to 15 decimal digits. The precision of four byte numbers is processor dependent.
You can specify the binary precision (number of significant bits) for a floating point value using the following optional syntax:
float(n)
where n is a value from 0 to 53. Storage is allocated according to the precision that is specified, as follows:
Range of Binary Precision | Storage Allocated |
|---|---|
0 to 23 | 4-byte float |
24 to 53 | 8-byte float |
Float Point Limitations
Users must consider the effects of data type conversions when numeric values are combined or compared. This is especially true when dealing with floating point values.
Exact matches on floating point numbers are discouraged, because float and float4 data types are approximate numeric values. In contrast, integer and decimal data types are exact numeric values.
Last modified date: 12/20/2023