How to rename a table along with projections in Vertica?

Question

I have to alter many columns in a Vertica table, and decided to drop the table altogether and create a new one. I also need 'undo' scripts ready to revert back the changes if needed (using mybatis migrations).

This is my plan:

rename mytable to mytable_backup

create mytable
create projection mytable_super (as select from mytable)


--undo
drop mytable if exists
rename mytable_backup to mytable

The original mytable was also created with a projection. The above script gives an error saying projection already exists.

 DBCException: SQL Error [4482] [42710]: [Vertica][VJDBC](4482) ROLLBACK:         Projection with base name "mytable_super" already exists

I believe when I rename the original table, the underlying projection is not being renamed.

What is the best way to rename a table with projections in vertica? Or what is the best way to back up a table and revert back?

Answer 1

You'll need to rename the projections as well.

alter projection mytable_super rename to mytable_super_backup;