Alter table to change the datatype of one column

[Jess.mic]

How can I alter the table to change the datatype of one column.

For e.g. I need to change the datatype of column from varchar to integer. How can I change that??

[id10t]

Hi!


>> How can I alter the table to change the datatype of one column.
VARCHAR to INTEGER? You can't, coz' it require storage reorganization.

Changing a column's data type

You can changes a table column's data type for any type whose conversion does not require storage reorganization. For example, the following types are the conversions that Vertica supports:

Binary types — expansion and contraction but cannot convert between BINARY and VARBINARY types.
Character types — all conversions allowed, even 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 precision, but you can change the scale in the ranges (0-18), (19-37), and so on.

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 can expand (and shrink) columns within the same class of data type, which is useful if you want to store longer strings in a column. Vertica validates the data before it performs the conversion.

For example, if you try to convert a column from varchar(25) to varchar(10) and that column holds a string with 20 characters, the conversion will fail. Vertica allow the conversion as long as that column does not have a string larger than 10 characters.