An arithmetic operation combines two or more numeric expressions using the arithmetic operators (see Arithmetic Operators) to form a resulting numeric expression.

Before an arithmetic operation is performed, the participating expressions are converted to identical data types. After the arithmetic operation is performed, the resulting expression also has that storage format.

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:

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:

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:

To convert one data type to another, use data type conversion functions (see Conversion Functions).

Vector uses the Standard decimal handling rules (decimal_rule in config.dat is set to Standard rather than Classic).

In expressions that combine decimal values and return decimal results, the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the Standard result can be determined, as shown in the following table:

If the result precision is larger than 38, the result scale is reduced by the amount of the precision excess, and the result precision is set to 38. The result scale is not reduced below 4 if the input scales are 4 or more; if both input scales are less then 4, the result scale is not reduced to less than the larger.

For example, in the following decimal addition operation:

the scale and precision of the result is calculated as follows:

Precision = 7, calculated as 3 (largest number of fractional digits) + 3 (largest number of non-fractional digits) + 1 = 7.

Scale = 3. The first operand has the largest number of digits to the right of the decimal point.

Result: 0569.124

If exponentiation is performed on a decimal value, the resulting data type is float.

Decimal arithmetic is handled according to the setting on the decimal_rule parameter in config.dat--either Classic or Standard.

The following examples compare Classic and Standard decimal handling. Given the input precision and scale, the result precision and scale for Classic and Standard settings are shown.

The Classic rules follow the SQL Standard specifications for result scale, even when the precision has to be limited to the maximum of 38. The effect is that in a complex expression, the result scale tends to be large, which reduces the number of available non-fractional digits and makes numeric overflow more likely.

The Standard rules reduce both precision and scale when precision gets too large, which favors non-fractional digits at the expense of fractional digits and makes numeric overflow less likely.

The following arithmetic is supported for date and time data types.

The result will be the same as the absolute term.

The combining rules are:

ANSI date and time values can be compared only to values of the same type. When the time zone setting of a time or timestamp value does not match that of its comparand, time or timestamp values WITH TIME ZONE or WITH LOCAL TIME ZONE are considered to be stored in GMT and can be compared directly. If one comparand is time or timestamp WITH TIME ZONE or WITH LOCAL TIME ZONE and the other comparand is WITHOUT TIME ZONE, the value of the WITHOUT TIME ZONE comparand is assumed to be in local time of the session default time zone. It is converted to GMT based on that assumption and the comparison proceeds.

The implicit coercion rules for operators are as follows: