I have the use case where I need to regularly fully drop and recreate a table in Vertica. To try to keep the period without data to a minimum, I want to load data in an intermediate table, then rename the old table out of the way, and rename the intermediate to its final name. It turns out that Vertica allows this in one command, hopefully thus avoiding race conditions.
After loading, before renaming:
vertica=> select * from important; v ----- old (1 row) vertica=> select * from important_intermediate ; v ----- new (1 row)
ALTER TABLE important,important_intermediate RENAME TO important_old, important;
vertica=> select * from important; v ----- new (1 row) vertica=> select * from important_old; v ----- old (1 row)
You will probably want to DROP import_old now.
Quick question: what becomes of the projections from the original ‘important’, do they work with the new ‘important’ as though it were the original? Also, do you need to re-run Database Designer optimization scripts after the re-name?
Any projections would follow the table. You would not need to re-run DBD.
That is very cool! Thank you for replying.