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 The For related information, see |
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. If you omit 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:
- The
AUTHORIZATION
statement indicates all tables are owned by the specified user. CREATE SCHEMA
statement and all associated sub-statements are treated as a single transaction. If any statement fails, Vertica rolls back the entireCREATE SCHEMA
statement.
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;