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.

 



Data Types		 Conversion 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
TIMESTAMPTZ
DATE
TIME
TIMETZ
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
LONG VARBINARY
     
LONG VARCHAR
 
VARCHAR
 
CHAR
FLOAT
LONG VARCHAR
 
BOOLEAN
INTEGER
NUMERIC
TIMESTAMP
TIMESTAMPTZ
DATE
TIME
TIMETZ
UUID
BINARY
VARBINARY
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH
     
VARCHAR
 
CHAR
 
FLOAT
LONG VARCHAR
VARCHAR
 
BOOLEAN
INTEGER
NUMERIC
TIMESTAMP
TIMESTAMPTZ
DATE
TIME
TIMETZ
UUID*
BINARY
VARBINARY
INTERVAL DAY/SECOND
INTERVAL YEAR/MONTH

* CHAR length ≥ 36

    
CHAR
 
TIMESTAMP
 
TIMESTAMPTZ
   
LONG CHAR
VARCHAR
CHAR
DATE
TIME
   
TIMESTAMP
 
TIMESTAMPTZ
 
TIMESTAMP
   
LONG CHAR
VARCHAR
CHAR
DATE
TIME
TIMETZ
   
TIMESTAMPTZ
 
DATE
 
TIMESTAMP
   
LONG CHAR
VARCHAR
CHAR
TIMESTAMPTZ
     
TIME
 
TIMETZ
 
TIMESTAMP
TIMESTAMPTZ
INTERVAL DAY/SECOND
 
LONG CHAR
VARCHAR
CHAR
   
TIME
 
TIMETZ
   
TIMESTAMP
TIMESTAMPTZ
 
LONG CHAR
VARCHAR
CHAR
TIME
   
TIMETZ
 
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
 
UUID
   
CHAR(36)

VARCHAR
     
UUID

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, (INTEGER + 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.

See Also