CREATE SCHEMA

Defines a schema.

Syntax

CREATE SCHEMA [ IF NOT EXISTS ] schema 
... [ AUTHORIZATION username] 
... [ DEFAULT { INCLUDE | EXCLUDE } [ SCHEMA ] PRIVILEGES ]

Parameters

IF NOT EXISTS

Specifies to generate an informational message if an object already exists under the specified name. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist, and reuse the existing object if it does.

For related information, see ON_ERROR_STOP.

schema Specifies the name of the schema to create, where schema conforms to conventions described in Identifiers.
AUTHORIZATION username Assigns ownership of the schema to a user. If a user name is not provided, the user who creates the schema is assigned ownership. Only superusers can create a schema that is owned by another user.
DEFAULT {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES

Specifies whether to enable or disable inheritance of privileges for tables in the schema. INCLUDE PRIVILEGES grants the tables in the schema the same privileges granted to the schema. 

If you omit INCLUDE PRIVILEGES, you must grant privileges individually for each table in the schema.

For more information see Grant Inherited Privileges.

Privileges

Optionally, CREATE SCHEMA can include the following sub-statements to create tables within the schema:

These sub-statements are treated as if they were entered as individual commands after CREATE SCHEMA executes. The following exceptions apply:

Examples

The following example creates a schema named s1 with no objects.

=> CREATE SCHEMA s1;

The following command creates schema s2 if it does not already exist:

=> CREATE SCHEMA IF NOT EXISTS schema2;

If the schema already exists, Vertica returns a rollback message:

=> CREATE SCHEMA IF NOT EXISTS schema2;
NOTICE 4214:  Object "schema2" already exists; nothing was done

The following series of commands create a schema named s1 with a table named t1 and grants Fred and Aniket access to all existing tables and ALL privileges on table t1:

=> CREATE SCHEMA s1;
=> CREATE TABLE t1 (c INT);
=> GRANT USAGE ON SCHEMA s1 TO Fred, Aniket;
=> GRANT ALL ON TABLE t1 TO Fred, Aniket;

This example sets the default behavior for new table t2 to automatically inherit the schema's privileges:

=> CREATE 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:

=> CREATE SCHEMA s2 DEFAULT EXCLUDE SCHEMA PRIVILEGES;