Schema Commands

A SCHEMA is a logical container for objects such as tables, views, sequences, indices, functions, procedures, aggregates and triggers. Schema access and modification is strictly controlled using the user role and authorization scheme.

A database contains multiple schemas. Initially a database already contains several system schemas such as sys, tmp, profiler, etc. which are required by the system. You can view the existing schemas in your database by running catalog query: SELECT * FROM sys.schemas;
One reason for organizing your data in schemas rather than databases is that in this way there will still be just one MonetDB process running serving multiple schemas each with their own tables. You can access the tables (or other objects) in different schemas by using the schema name prefix: myschema.myobjectname

create_schema:
 CREATE SCHEMA [ IF NOT EXISTS ] schema_name_clause
 [ DEFAULT CHARACTER SET ident ]
 [ PATH schema_name [','... ] ]
 [ schema_element [','... ] ]

schema_name_clause:
   schema_name
 | [ schema_name ] AUTHORIZATION auth_name

schema_element:
 grant | revoke | create_statement | drop_statement | alter_statement


get current schema:
 SELECT CURRENT_SCHEMA

set schema:
 SET SCHEMA schema_name


alter schema:
 ALTER SCHEMA [ IF EXISTS ] schema_name RENAME TO new_schema_name

drop schema:
 DROP SCHEMA [ IF EXISTS ] schema_name [ drop_action ]

drop_action:
 RESTRICT | CASCADE

One can create a new schema using the CREATE command and change to it, by using the SET command. When creating a table (or other object) without specifying the schema, the table (or object) will be created in the schema that is currently in use. You can retrieve the current schema name via query: SELECT CURRENT_SCHEMA; In order to create a table in a different schema, use the schema name as a prefix like: myschema.mytable such that it becomes a fully qualified name.

The AUTHORIZATION option allows specifying the name of the user or the role that will own the schema. If omitted, the user who has executed the query will be the owner. The owner of the schema is allowed to create, alter and drop tables in the schema. With the AUTHORIZATION option, an explicit name for the schema is optional. If omitted, the schema automatically gets the name of the authorized user/role as its name.

The auth_name can be either an existing role or user name.

A schema can be renamed only if it does not contain objects that are a dependency for objects outside the schema. Dependencies between objects can be queried via SELECT * FROM sys.dependencies_vw;.

Rules

1. The ownership of a schema can be assigned to only one user/role, and it can not be modified after its creation. Therefore, to share the ownership of a schema, one must assign the ownership of a schema to a role at the creation of the schema. Subsequently, the role can be granted to multiple users to own the schema.
2. Only the 'monetdb' user or users with the 'sysadmin' role can create a new schema. Therefore, to allow other users to create schemas, the 'monetdb' user should assign the 'sysadmin' role to the intended users.

Examples:

Create a schema:

CREATE SCHEMA tst;
CREATE SCHEMA AUTHORIZATION hrm;
CREATE SCHEMA hr AUTHORIZATION hrm;
SET SCHEMA tst;
SELECT CURRENT_SCHEMA;

Change the name of a schema:

CREATE SCHEMA tst;
ALTER SCHEMA tst RENAME TO tst2;

Associated system table/view: sys.schemas, information_schema.schemata