How to alter a column data type from numeric(3,2) to numeric(5,4) in Vertica

In Vertica, you can alter the precision, but you cannot alter the scale of the numeric:
https://www.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/ColumnManagement/ChangingAColumnsDataType.htm
Here are two options:
1- Create a new table with new column data type, load it with the data from exiting table, drop the old one and rename the new one.
2- Create a new column in the table, load the column with the data, drop the old column and rename the new one.

Answers

  • hsaxena20hsaxena20 Community Edition User

    You cannot alter a column data type if it present in projections segmentation or order by clause. First create a super projections without having that column in segmentation and order by. Second, refresh the table. Third, Delete the earlier superprojection.
    Now you can able to change the data type.

Sign In or Register to comment.