Changing column data type

In general, you can change a column's data type with ALTER TABLE if doing so does not require storage reorganization.

In general, you can change a column's data type with ALTER TABLE 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.

The sections that follow describe requirements and restrictions associated with changing a column's data type.

Supported data type conversions

Vertica supports conversion for the following data types:

Data Types Supported Conversions
Binary Expansion and contraction.
Character All conversions between CHAR, VARCHAR, and LONG VARCHAR.
Exact numeric

All conversions between the following numeric data types: integer data types—INTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT—and NUMERIC values of scale <=18 and precision 0.

You cannot modify the scale of NUMERIC data types; however, you can change precision in the ranges (0-18), (19-37), and so on.

Collection

The following conversions are supported:

  • Collection of one element type to collection of another element type, if the source element type can be coerced to the target element type.
  • Between arrays and sets.
  • Collection type to the same type (array to array or set to set), to change bounds or binary size.

For details, see Changing Collection Columns.

Unsupported data type conversions

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

  • Boolean

  • DATE/TIME

  • Approximate numeric type

  • BINARY to VARBINARY and vice versa

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.

  • Complex type column. One exception applies: in external tables, you can change a primitive column type to a complex type.

You can work around some of these restrictions. For details, see Working with column data conversions.

In this section