ALTER SCHEMA
Renames one or more existing schemas.
Caution: Renaming a schema referenced by a view causes the view to fail unless another schema is created to replace it.
Syntax
ALTER SCHEMA [schema-name[,...] RENAME TO new-schema-name [,...] [ DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES ]
Parameters
schema-name |
Specifies a schema to rename. |
new-schema-name |
Specifies one or more new schema names. When renaming schemas, be sure that:
Vertica applies the new schema names in the RENAME TO parameter atomically, renaming either all schemas or none. For example, if the number of schemas to rename does not match the number of new names you supply, no schemas are renamed. |
DEFAULT {INCLUDE | EXCLUDE} SCHEMA PRIVILEGES
|
Specifies whether to enable or disable default privileges for new tables in the renamed schema.
For more information see Grant Inherited Privileges. |
Privileges
Schema owner or user requires CREATE privilege on the database
Swapping Schemas
Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. For example, the following ALTER SCHEMA
statement uses the temporary schema temps to facilitate swapping schema S1 with schema S2. In this example, S1 is renamed to temps. Then S2 is renamed to S1. Finally, temps is renamed to S2.
ALTER SCHEMA S1, S2, temps RENAME TO temps, S1, S2;
Examples
The following example renames schemas S1
and S2
to S3
and S4
, respectively:
=> ALTER SCHEMA S1, S2 RENAME TO S3, S4;
This example sets the default behavior for new table t2
to automatically inherit the schema's privileges:
=> ALTER SCHEMA s1 DEFAULT INCLUDE SCHEMA PRIVILEGES;
=> CREATE TABLE s1.t2 (i, int);
This example sets the default for new tables to not automatically inherit privileges from the schema:
=> ALTER SCHEMA s1 DEFAULT EXCLUDE SCHEMA PRIVILEGES;