4. Understanding the Elements of SQL Statements : SQL Operations : Arithmetic Operations : Default Type Conversion
 
Share this page                  
Default Type Conversion
When two numeric expressions are combined, the data types of the expressions are converted to be identical; this conversion determines the data type of the result. The expression having the data type of lower precedence is converted to the data type of higher precedence.
The order of precedence among the numeric data types, in highest-to-lowest order, is as follows:
money
float4
float
decimal
integer8 (bigint)
integer4 (integer)
integer2 (smallint)
integer1 (tinyint)
For example, in an operation that combines an integer and a floating point number, the integer is converted to a floating point number before the operation is performed. If the operands are two integers of different sizes, the smaller is converted to the size of the larger.
For example, for the expression:
(job.lowsal + 1000) * 12
the first operator (+) combines a float4 expression (job.lowsal) with a smallint constant (1000). The result is float4. The second operator (*) combines the float4 expression with a smallint constant (12), resulting in a float4 expression.
The following table lists the data types that result from combining numeric data types in expressions:
integer1
integer2
integer4
integer8
decimal*
float8
float4
money
integer1
integer8
integer8
integer8
integer8
decimal6
float8
float4
money
integer2
integer8
integer8
integer8
integer8
decimal6
float8
float4
money
integer4
integer8
integer8
integer8
integer8
decimal12
float8
float4
money
integer8
integer8
integer8
integer8
integer8
decimal20
float8
float4
money
decimal*
decimal6
decimal6
decimal12
decimal20
decimal6
float8
float4
money
float8
float8
float8
float8
float8
float8
float8
float4
money
float4
float4
float4
float4
float4
float4
float4
float4
money
money
money
money
money
money
money
money
money
money
 
*decimal(n) – The result size depends on the size of the decimal. The result size shown is for adding decimal(1) to the other value, for example: decimal(1) + int2(1).
To convert one data type to another, use data type conversion functions (see Conversion Functions).