CODEWITHSUNDEEP
vertica
Gadam
Gadam

Reputation: 3024

How to rename a table along with projections in Vertica?

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?

Upvotes: 2

Views: 4108

Answers (2)

aryan singh
aryan singh

Reputation: 161

For renaming a table, the above answer is the one.

One way to rename a projection, would be to get the projection inside a SQL file. For example:

 select CONCAT(CONCAT(projection_schema,'.'),projection_name) from projections where projection_schema like '%one_table%'

Then modify it into the following SQL and execute it (and don't forget to run refresh): https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/UpdatingProjectionsUsingRefresh.htm

Once you have the SQL, you can do \i /path/of/sql (inside vertica shell) or you can /opt/vertica/bin/vsql -f /path/to/sql/that.sql -U vertica_user -w vertica_passwd

Upvotes: 0

woot
woot

Reputation: 7606

You'll need to rename the projections as well. 

alter projection mytable_super rename to mytable_super_backup;

Upvotes: 2

Related Questions