Data type promotion

In a set operation, if the corresponding columns in the input tables are not exactly the same, Netezza Performance Server for Cloud Pak for Data System promotes the data type. The following tables list the possible promotions.

In the following tables, the table cell represents the data type of the output table and a dash (-) means that the pair has no data type promotion logic.

Table 1. Data type promotion with numbers and characters for UNION: Part 1
	INT1	INT2	INT4	INT8	NUMERIC
INT1	INT1	INT2	INT4	INT8	NUMERIC
INT2	INT2	INT2	INT4	INT8	NUMERIC
INT4	INT4	INT4	INT4	INT8	NUMERIC
INT8	INT8	INT8	INT8	INT8	NUMERIC
NUMERIC	NUMERIC	NUMERIC	NUMERIC	NUMERIC	NUMERIC
REAL	REAL	REAL	REAL	REAL	DOUBLE
DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE
CHAR	-	-	-	-	-
VARCHAR	-	-	-	-	-
NCHAR	-	-	-	-	-
NVARCHAR	-	-	-	-	-

Table 2. Data type promotion with numbers and characters for UNION: Part 2
	REAL	DOUBLE	CHAR	VARCHAR	NCHAR	NVARCHAR
INT1	REAL	DOUBLE	-	-	-	-
INT2	REAL	DOUBLE	-	-	-	-
INT4	REAL	DOUBLE	-	-	-	-
INT8	REAL	DOUBLE	-	-	-	-
NUMERIC	DOUBLE	DOUBLE	-	-	-	-
REAL	REAL	DOUBLE	-	-	-	-
DOUBLE	DOUBLE	DOUBLE	-	-	-	-
CHAR	-	-	CHAR	VARCHAR	NCHAR	NVARCHAR
VARCHAR	-	-	VARCHAR	VARCHAR	NCHAR	NVARCHAR
NCHAR	-	-	NCHAR	NCHAR	NCHAR	NCHAR
NVARCHAR	-	-	NVARCHAR	NVARCHAR	NVARCHAR	NVARCHAR

The following table displays the data type promotion for non-integer values.

Table 3. Data type promotion with non-integers
	UNKNOWN	BOOL	DATE	TIME	TIMESTAMP	TIMETZ	INTERVAL
BOOL	BOOL	BOOL	-	-	-	-	-
DATE	DATE	DATE	-	DATE	-	-	-
TIME	TIME	-	TIME	TIME	TIME	-	-
TIMESTAMP	TIMESTAMP	-	TIMESTAMP	TIMESTAMP	TIMESTAMP	TIMESTAMP	-
TIMETZ	TIMETZ	-	-	TIMETZ	TIMETZ	TIMETZ	-
INTERVAL	INTERVAL	-	-	-	-	-	INTERVAL