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 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 object) 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 other 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.

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

The DEFAULT CHARACTER SET and PATH options are here for compatibility reasons with the SQL standard, however they are not (yet) implemented. The default character set is UTF-8 for all schemas.

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. 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.

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;

It is only allowed to change the name of a schema if no objects exists which depend on the schema name, such as tables, views, functions, etc.

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