A SCHEMA is a logical container for objects such as tables, views, indices, sequences, triggers, functions, aggregates and procedures. 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
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
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 the SQL standard, however they are not (yet) implemented. The default character set is UTF-8.
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.
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: sys.schemas