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, and the remaining columns indicate the result for the different conversion types.
| Source Data Type | 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 (CHAR length ≥ 36), BINARY, VARBINARY, INTERVAL DAY/SECOND, INTERVAL YEAR/MONTH | 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.