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;
or from SQL standard view: SELECT * FROM information_schema.schemata;

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

change current schema:
  SET SCHEMA schema_name


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

add/remove a schema comment:
  COMMENT ON SCHEMA schema_name IS  { 'comment text' | NULL | '' }

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, include the schema name as a prefix of the table name like: myschema.mytable or "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 "DWH";
COMMENT ON SCHEMA "DWH" IS 'DataWareHouse v4.2';

CREATE ROLE hrm;
CREATE SCHEMA AUTHORIZATION hrm;
CREATE SCHEMA "HR" AUTHORIZATION hrm;
COMMENT ON SCHEMA "HR" IS 'Human Resources application data';

SET SCHEMA "DWH";
SELECT CURRENT_SCHEMA;

SET SCHEMA "HR";
SELECT CURRENT_SCHEMA;

SET SCHEMA hrm;
SELECT CURRENT_SCHEMA;

SELECT * FROM sys.schemas where not system order by name;

SELECT schema_name, schema_owner, default_character_set_name, comments FROM information_schema.schemata where not is_system;

Change the name of a schema:

CREATE SCHEMA tst;
ALTER SCHEMA tst RENAME TO tst2;

Drop a schema:

SET SCHEMA sys;
DROP SCHEMA tst2 RESTRICT;
DROP SCHEMA IF EXISTS tst3 CASCADE;

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