Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

CREATE SCHEMA statement

The CREATE SCHEMA statement creates a schema in the current catalog.

A schema is a system object that serves as a container for ordinary objects such as graph types, graphs, and procedures.

When a service group is initiated, a schema /default_schema is automatically created as the default schema to store resource objects of graphs. This schema is assigned as both the home schema for the current user and the schema for the current session when the session begins.

The user who creates a schema becomes its owner and has implicit USAGE on it. Other users must be granted USAGE before they can discover, select, or access the schema. For more information, see Schema access control.

  • To change the home schema for the current user, use the ALTER USER.
  • To change the schema for the current session, use the SESSION SET.

Prerequisites

To create a schema, you must be granted to perform the action CREATE SCHEMA in the current catalog. For more information, see Roles and privileges.

Syntax

create_schema_statement
CREATE SCHEMA IF NOT EXISTS schema_reference 

create_schema_statement ::=
    'CREATE' 'SCHEMA' ('IF NOT EXISTS')? schema_reference

Parameters

IF NOT EXISTS

This clause checks if a schema already exists in the same path. If it does already exist, the schema is not created and no exception is thrown.

If you do not specify this clause, an exception is thrown if a schema already exists in the same path.

schema_reference

The reference to the path of the schema. It specifies the path of the schema to be created.

Both absolute and relative schema paths can be used in the statement.

  • Absolute paths must start with /. For example, /test1 and /test2/test3.
  • Relative paths must start with ../. For example, ../test1 and ../test2/test3.

When a relative path is used, the corresponding absolute path is determined based on the schema set in the current session. For example:

  • If the schema in the current session is set to /default_schema, which has a path depth of 1:
    • the relative path ../test1 resolves to the absolute path /test1.
    • the relative path ../test2/test3 resolves to the absolute path /test2/test3.
  • If the schema in the current session is set to /my_test/test0, which has a path depth of 2:
    • the relative path ../test1 resolves to the absolute path /my_test/test1.
    • the relative path ../test2/test3 resolves to the absolute path /my_test/test2/test3.
    • the relative path ../../test4 resolves to the absolute path /test4.

In Unix-like systems, traversing above the root directory (/) using ../ has no effect since root is the topmost directory. Therefore, when resolving relative paths, any ../ that would move the path above the root path / is ignored. For example, if the schema in the current session is set to /default_schema, which has a path depth of 1, relative paths ../../test1 and ../../../test1 are equivalent to ../test1, all resolving to the absolute path /test1.

Note the following limits:

  • The path of the schema to be created must not conflict with that of an existing schema. For example, if a schema already exists in the path /my_test, you cannot create other schemas in its subpath, such as /my_test/test1 or /my_test/test1/test2.
  • The path /tmp_schema is reserved by the system and cannot be used to create a schema.

Examples

Create a schema using absolute path

CREATE SCHEMA IF NOT EXISTS /test/my_schema1

The preceding example creates a schema in the path /test/my_schema1.

Create a schema using relative path

'''
// Create a schema
CREATE SCHEMA IF NOT EXISTS /test/my_schema1
'''

'''
// Set the schema for the current session
SESSION SET SCHEMA /test/my_schema1
'''

'''
// Create a schema using relative path
CREATE SCHEMA IF NOT EXISTS ../my_schema2
'''

The preceding example creates a schema in the path /test/my_schema2 when the schema in the current session is set to /test/my_schema1.

More information