Promotion of data types

Data types can be classified into groups of related data types. Within such groups, an order of precedence exists in which one data type is considered to precede another data type. This precedence enables DB2® to support the promotion of one data type to another data type that appears later in the precedence order.

For example, DB2 can promote the data type CHAR to VARCHAR and the data type INTEGER to DOUBLE PRECISION; however, DB2 cannot promote a CLOB to a VARCHAR.

DB2 considers the promotion of data types when:

Performing function resolution (see Function resolution)
Casting distinct types (see Casting between data types)
Assigning built-in data types to distinct types (see Distinct type assignments)

For each data type, the following table shows the precedence list (in order) that DB2 uses to determine the data types to which the data type can be promoted. The table indicates that the best choice is the same data type and not promotion to another data type. The table also shows data types that are considered equivalent during the promotion process. For example, CHARACTER and GRAPHIC are considered to be equivalent data types.

Table 1. Precedence of data types
Data type^1,2	Data type precedence list (in best-to-worst order)
SMALLINT³	SMALLINT, INTEGER, BIGINT, decimal, real, double, DECFLOAT
INTEGER³	INTEGER, BIGINT, decimal, real, double, DECFLOAT
BIGINT³	BIGINT, decimal, real, double, DECFLOAT
decimal³	decimal, real, double, DECFLOAT
real	real, double, DECFLOAT
double	double, DECFLOAT
DECFLOAT	DECFLOAT
CHAR or GRAPHIC	CHAR or GRAPHIC, VARCHAR or VARGRAPHIC, CLOB or DBCLOB
VARCHAR or VARGRAPHIC	VARCHAR or VARGRAPHIC, CLOB or DBCLOB
CLOB or DBCLOB	CLOB or DBCLOB
BINARY	BINARY, VARBINARY, BLOB
VARBINARY	VARBINARY, BLOB
BLOB	BLOB
DATE	DATE
TIME	TIME
TIMESTAMP WITHOUT TIME ZONE	TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITH TIME ZONE
ROWID	ROWID
XML	XML
A distinct type	The same distinct type
Notes: The data types in lowercase letters represent the following data types: decimal DECIMAL(`p`,`s`) or NUMERIC(`p`,`s`) real REAL or FLOAT(`n`) where `n` is not greater than 21 double DOUBLE, DOUBLE PRECISION, FLOAT or FLOAT(`n`) where `n` is greater than 21 Other synonyms for the listed data types are considered to be the same as the synonym listed. Real and double are checked for function resolution purposes only. Additionally, the number of significant digits (even for DECFLOAT(16)), and the exponent range of DECFLOAT exceeds that of real and double (double has 16 significant digits). Therefore, DECFLOAT values will not be promoted to real or double.