Changing a Column Data Type

You can change a table column's data type with ALTER TABLE as long as 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.

You can expand columns within the same class of data type, This is useful for storing longer strings in a column. Vertica validates the data before it performs the conversion. In general, you can also reduce column widths within the data type class, as long as existing column data is no greater than the new width. For details, see Reducing Column Width.

Unsupported Data Type Conversions

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

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

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