Data Type Conversion Requirements

You can change a table column's data type with ALTER TABLE if the change complies with the requirements and restrictions cited below.

Supported Data Type Conversions

You can change a column's data type if doing so does not require storage reorganization. After you modify a column's data type, data that you load conforms to the new definition.

Vertica supports conversion between the following data types:

Data Types Notes
Binary types

Expansion and contraction allowed.

Conversion not allowed between BINARY and VARBINARY types.
Character types All conversions allowed, including between CHAR and VARCHAR
Exact numeric types

INTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT, and all NUMERIC values of scale <=18 and precision 0 are interchangeable.

For NUMERIC data types, you cannot alter scale, but you can change the precision in the ranges (0-18), (19-37), and so on.

Unsupported Data Type Conversions

Vertica does not allow data type conversion on types that require storage reorganization:

  • Boolean type conversion to other types
  • DATE/TIME type conversion
  • Approximate numeric type conversions
  • Between BINARY and VARBINARY types

You also cannot change a column's data type if the column is one of the following:

  • Primary key
  • Foreign key
  • Included in the SEGMENTED BY clause of any projection for that table.

You can work around some of these restrictions. For details, see Working With Column Data Conversions.