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:

  • New schema names are not already in use.
  • The number of schemas to rename corresponds with the number of new schema names.

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.

INCLUDE SCHEMA PRIVILEGES specifies to grant tables in the renamed schema the same privileges granted to that schema. This option has no effect on tables that were created in the schema before it was renamed.

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;