Numeric Data Types
Numeric data types are in two categories: exact and approximate.
• Exact types include integer and decimal data types.
• Approximate types include floating point data types.
Integer Data Types
Exact numeric data types include the following integer data types:
• tinyint (one-byte)
• smallint (two-byte)
• integer (four-byte)
• bigint (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 Type
The decimal data type is an exact numeric data type defined by its precision (total number of digits) and scale (number of digits to the right of the decimal point). For example:
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. For display purposes, a currency sign cannot be specified for decimal values.
Specify the decimal data type using the following syntax:
decimal(p,s)
where:
p
Defines the precision. Minimum is 1; maximum is 39 (38 for X100).
s
Defines the scale. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).
Note: Synonyms for the decimal data type are dec and numeric.
The following defines a type with precision of 23 decimal digits, 5 of which are after the decimal point:
decimal(23,5)
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)
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 fifteen decimal digits. The precision of four-byte numbers is processor dependent.
You can specify the minimum required 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 based on the precision that is specified, as follows:
Range of Binary Precision | Storage Allocated | Example |
|---|---|---|
0 to 23 | 4-byte float | float(18) defines a floating point type with at least 18 binary digits of precision in the mantissa. A 4‑byte floating point field is allocated for it, which has 23 bits of precision. |
24 to 53 | 8-byte float | float(41) defines a floating point type with at least 41 binary digits of precision in the mantissa. A 8‑byte floating point field is allocated for it, which has 53 bits of precision. |
Floating point precision is not limited to the declared size.
Float Point Limitations
You 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.
Here is an example of why it is hard to find an exact match on a floating point number:
CREATE TABLE test(f8 FLOAT8);
INSERT INTO test VALUES(330/11.0);
SELECT * FROM test WHERE f8 = 30;
returns no rows.
On inspection,
SELECT HEX(f8) FROM test;
returns:
+----------------+
|col1 |
+----------------+
|403E000000000001|
+----------------+
Using an IEEE analyzer we find this number to be decimal 30.000000000000003552713678800500929355621337890625, which, though close to 30, shows why no exact match was found.
To ensure the query returns a row, you can change it to the following:
SELECT * FROM test WHERE ABS(f8 - 30) < 0.0000000001
Last modified date: 01/30/2023