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:
- 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.