CREATE SCHEMA

Defines a schema.

Syntax

CREATE SCHEMA [ IF NOT EXISTS ] [database.]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.

[database.]schema

Identifies the schema to create, where schema conforms to conventions described in Identifiers. This name must be unique among all other schema names in the database.

If you specify a database, it must be the current database.
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 default inheritance of privileges for new tables in the specified schema:

  • EXCLUDE SCHEMA PRIVILEGES (default): Disables inheritance of schema privileges.
  • INCLUDE SCHEMA PRIVILEGES: Specifies to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

If you omit INCLUDE PRIVILEGES, you must explicitly grant schema privileges on the desired tables.

For more information see Enabling Schema Inheritance.

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

Create schema s1:

=> CREATE SCHEMA s1;

Create schema s2 if it does not already exist:

=> CREATE SCHEMA IF NOT EXISTS s2;

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

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

Create table t1 in schema s1, then grant users Fred and Aniket access to all existing tables and all privileges on table t1:

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

Enable inheritance on new schema s3 so all tables created in it automatically inherit its privileges. In this case, new table s3.t2 inherits USAGE, CREATE, and SELECT privileges, which are automatically granted to all database users:

=> CREATE SCHEMA s3 DEFAULT INCLUDE SCHEMA PRIVILEGES;
CREATE SCHEMA
=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA S3 TO PUBLIC;
GRANT PRIVILEGE
=> CREATE TABLE s3.t2(i int);
WARNING 6978: Table "t2" will include privileges from schema "s3"
CREATE TABLE

See Also