Moving Tables to Another Schema
ALTER TABLE…SET SCHEMA moves a table from one schema to another. Vertica automatically moves all projections that are anchored to the source table to the destination schema. It also moves all
AUTO_INCREMENT columns to the destination schema.
Moving a table across schemas requires that you have
USAGE privileges on the current schema and
CREATE privileges on destination schema. You can move only one table between schemas at a time. You cannot move temporary tables across schemas.
If a table of the same name or any of the projections that you want to move already exist in the new schema, the statement rolls back and does not move either the table or any projections. To work around name conflicts:
- Rename any conflicting table or projections that you want to move.
ALTER TABLE…SET SCHEMAagain.
Vertica lets you move system tables to system schemas. Moving system tables could be necessary to support designs created through the Database Designer.
The following example moves table
T1 from schema
S1 to schema
S2. All projections that are anchored on table
T1 automatically move to schema
=> ALTER TABLE S1.T1 SET SCHEMA S2;