Data Type Coercion Chart

Conversion Types

The following table defines all possible type conversions that Vertica supports. The data types in the first column of the table are the inputs to convert, while data types listed across the second heading row indicate the resultant assignments.

Conversion Types
Data Types Implicit Explicit Assignment Assignment without numeric meaning Conversion without explicit casting
BOOLEAN 
   
INTEGER

LONG VARCHAR

VARCHAR

CHAR
   
INTEGER
BOOLEAN

NUMERIC

FLOAT
 
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
LONG VARCHAR
VARCHAR
CHAR
 
NUMERIC
FLOAT
 
INTEGER
LONG VARCHAR
VARCHAR
CHAR
NUMERIC
FLOAT
   
INTEGER
NUMERIC
LONG VARCHAR
VARCHAR
CHAR
 
LONG VARCHAR
FLOAT
CHAR
BOOLEAN
INTEGER
NUMERIC
VARCHAR
TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATE
TIME
TIME WITH TIME ZONE
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
LONG VARBINARY
   
LONG VARCHAR
VARCHAR
FLOAT
LONG VARCHAR
CHAR
BOOLEAN
INTEGER
NUMERIC
TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATE
TIME
TIME WITH TIME ZONE
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
   
VARCHAR
CHAR
FLOAT
LONG VARCHAR
VARCHAR
BOOLEAN
INTEGER
NUMERIC
TIMESTAMP
TIMESTAMP WITH TIME ZONE
DATE
TIME
TIME WITH TIME ZONE
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
   
CHAR
TIMESTAMP
TIMESTAMP WITH TIME ZONE
 
LONG CHAR
VARCHAR
CHAR
DATE
TIME
 
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP
 
LONG CHAR
VARCHAR
CHAR
DATE
TIME
TIME WITH TIME ZONE
 
TIMESTAMP WITH TIME ZONE
DATE
TIMESTAMP
 
LONG CHAR
VARCHAR
CHAR
TIMESTAMP WITH TIME ZONE
   
TIME
TIME WITH TIME ZONE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
INTERVAL DAY/SECOND
LONG CHAR
VARCHAR
CHAR
 
TIME
TIME WITH TIME ZONE
 
TIMESTAMP
TIMESTAMP WITH TIME ZONE
LONG CHAR
VARCHAR
CHAR
TIME
 
TIME WITH TIME ZONE
INTERVAL 
DAY/SECOND
 
TIME
INTEGER
LONG CHAR
VARCHAR
CHAR
 
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
   
INTEGER
LONG CHAR
VARCHAR
CHAR
 
INTERVAL YEAR/MONTH
LONG VARBINARY
 
VARBINARY
   
LONG VARBINARY
VARBINARY
LONG VARBINARY
BINARY
     
VARBINARY
BINARY
VARBINARY
     
BINARY

Implicit and Explicit Conversion

Vertica supports data type conversion of values without explicit casting, such as NUMERIC(10,6) -> NUMERIC(18,4). Implicit data type conversion occurs automatically when converting values of different, but compatible, types to the target column's data type. For example, when adding values, (INT + NUMERIC), the result is implicitly cast to a NUMERIC type to accommodate the prominent type in the statement. Depending on the input data types, different precision and scale can occur.

An explicit type conversion must occur when the source data cannot be cast implicitly to the target column's data type.

Assignment Conversion

In data assignment conversion, coercion implicitly occurs when values are assigned to database columns in an INSERT or UPDATE..SET statement. For example, in a statement that includes INSERT ... VALUES('2.5'), where the target column data type is NUMERIC(18,5), a cast from VARCHAR to the column data type is inferred.

In an assignment without numeric meaning, the value is subject to CHAR/VARCHAR/LONG VARCHAR comparisons.