Vertica Create Schema command
How to create schema in Vertica ?
In Vertica like in other database the "SCHEMA" is a part of the Logical Schema.
Why the need of schemas ?
Allows Many users to access the database without interfering with one another.
Individual schemas can be configured to grant specific users access to the schema and its tables while restricting others.
Allows other applications to create tables that have the same name in different schemas, preventing table collisions.
The user who can create a schema is the superuser or a user that received the right to create one.
Syntax to create a schema
CREATE SCHEMA [ IF NOT EXISTS ] [db-name.]schema [ AUTHORIZATION user-name ]
Where the options are:
[ IF NOT EXISTS ] - Will generate an notice if object exist.
[db-name.] - Specifies the current database name.
schema - Specifies the name of the schema to create
AUTHORIZATION user-name - Assigns ownership of the schema to a user. Only a Superuser is allowed to create a schema that is owned by a different user.
Examples of creating a schema in Vertica:
Create schema :
CREATE SCHEMA schema_name;
Create a schema "IF NOT EXISTS" and schema does not exist:
CREATE SCHEMA IF NOT EXISTS schema_name;
Create a schema with the "IF NOT EXISTS " and schema exist:
CREATE SCHEMA IF NOT EXISTS schema_name;
NOTICE 4214: Object "schema_name" already exists; nothing was done
Create a schema where the tables owner will be Martin:
CREATE SCHEMA schema_name AUTHORIZATION Martin;
In the next tutorial we will see how we can alter a schema.
