CREATE SCHEMA

Function

CREATE SCHEMA defines a schema.

A schema logically organizes objects and data within a database. By managing schemas, multiple users can use the same database without interfering with each other, and third-party applications can be added to the corresponding schema without causing conflicts.

The core functions of a schema are:

• Logical isolation: Achieves isolation of data, functions, and tenants.
• Permission control: Provides fine-grained security boundaries.
• Organizational management: Keeps the database structure clear and maintainable.
• Namespace: Prevents object naming conflicts.

The same database object name can be used in different schemas within the same database without conflict. For example, both a_schema and b_schema can contain a table named mytable. Users with the required permissions can access objects in multiple schemas within the database.

When a user is created in the current database, the system automatically creates a schema with the same name as the user in the current database. For example, when creating user user1, a schema named user1 is created by default.

Precautions

• A schema can be created as long as the user has the CREATE privilege on the current database.
• Objects created by a system administrator in a schema that has the same name as a regular user are owned by the user with the same name as the schema (not the system administrator).
• When accessing a named object, the schema name can be used as a prefix. If no schema name prefix is used, the object in the current schema is accessed. When creating a named object, the schema name can also be used as a prefix modifier.
• CREATE SCHEMA can include subcommands for creating objects within the new schema. These subcommands are no different from commands issued after the schema is created. If the AUTHORIZATION clause is used, all created objects will be owned by that user.

Syntax

• Create a schema with a specified name:

  1 2 3 4

  CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ WITH PERM SPACE 'space_limit'] [ schema_element [ ... ] ]; CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ] [ WITH PERM SPACE 'space_limit'] ;

• Create a schema based on a username:

  1 2	CREATE SCHEMA AUTHORIZATION user_name [ WITH PERM SPACE 'space_limit'] [ schema_element [ ... ] ]; CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name [ WITH PERM SPACE 'space_limit'] ;

Parameter Description

Example: Basic Example

Create a role named role1.

1
2

DROP ROLE IF EXISTS role1;
CREATE ROLE role1 IDENTIFIED BY '{password}';

Create a schema named role1 for the role1 role. The owner of the films and winners tables created by the clause is role1.

1
2
3
4
5

DROP TABLE IF EXISTS role1.films;
CREATE SCHEMA AUTHORIZATION role1
     CREATE TABLE films (title text, release date, awards text[])      
     CREATE VIEW winners AS         
     SELECT title, release FROM films WHERE awards IS NOT NULL;

Example: Creating a Schema with Permanent Storage Space Set

The following example simulates creating a schema named testsche and setting the permanent storage space to 10 MB. When the data exceeds this set value, the system reports an error.

Alternatively, you can also set the schema permanent storage space through the DWS console. For details, see Configuring the Schema Storage Space of the DWS Database.

1. Create a schema named testsche and set its permanent storage space to 10 MB:

   1 2	DROP SCHEMA IF EXISTS testsche; CREATE SCHEMA testsche WITH PERM SPACE '10M';

2. Use the system catalog PG_NAMESPACE to query the storage space size of the schema, where permspace is the permanent storage space limit and usedspace is the used permanent storage space size:

   1	SELECT permspace,usedspace FROM PG_NAMESPACE WHERE nspname = 'testsche';

   

3. Create a test table and import data:

   1 2 3 4 5

   DROP TABLE IF EXISTS testsche.src; DROP TABLE IF EXISTS testsche.t1; CREATE TABLE testsche.src AS SELECT 1; CREATE TABLE testsche.t1(a int, b numeric(15,2)) WITH(orientation=column); INSERT INTO testsche.t1 SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM testsche.src;

   The system returns an error message indicating that the permanent storage space of the schema has been exceeded.

   

4. Increase the permanent storage space of the schema to 10 GB, and import again. The operation succeeds:

   1 2	ALTER SCHEMA testsche WITH PERM SPACE '10G'; INSERT INTO testsche.t1 SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM testsche.src;

   

5. View the used space:

   1	SELECT permspace,usedspace FROM PG_NAMESPACE WHERE nspname = 'testsche';

   

Helpful Links

ALTER SCHEMA, DROP SCHEMA

Creating and Managing Schemas

Configuring the Schema Storage Space of the DWS Database