Data type coercion chart

The following table defines all possible type conversions that Vertica supports.

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