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