SCHEMA

Schemas function as models. Schema management allows multiple users to use the same database without mutual impacts, to organize database objects as manageable logical groups, and to add third-party applications to the same schema without causing conflicts.

Each database has one or more schemas. Each schema contains tables and other types of objects. When a database is initially created, it has a schema named PUBLIC by default, and all users have the usage permission on the schema. Only the system administrator and initial users can create functions, stored procedures, and synonyms in the PUBLIC schema. Other users cannot create the three types of objects even if they have the create permission. You can group database objects by schema. A schema is similar to an OS directory but cannot be nested.

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

When you execute the CREATE USER statement to create a user, the system creates a schema with the same name as the user in the database where the statement is executed.

Syntax

• Create a schema.

  CREATE SCHEMA schema_name 
      [ AUTHORIZATION user_name ] ;
  

• Modify a schema.

  • Rename a schema.

    ALTER SCHEMA schema_name 
        RENAME TO new_name;
    

  • Change the owner of a schema.

    ALTER SCHEMA schema_name 
        OWNER TO new_owner;
    

• Delete a schema and its objects.

  DROP SCHEMA  schema_name;
  

  Schemas beginning with pg_temp or pg_toast_temp are for internal use. Do not delete them. Otherwise, unexpected consequences may be incurred.

Parameter Description

• schema_name

  Specifies the schema name.

  NOTICE: The name must be unique. The schema name cannot start with pg_.

  Value range: a string. It must comply with the identifier naming convention.

• AUTHORIZATION user_name

  Specifies the owner of a schema. If schema_name is not specified, user_name will be used as the schema name. In this case, user_name can only be a role name.

  Value range: an existing username or role name

• schema_element

  Specifies an SQL statement defining an object to be created within a schema. Currently, only the CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE PARTITION, CREATE SEQUENCE, CREATE TRIGGER and GRANT clauses are supported.

  Objects created by sub-commands are owned by the user specified by AUTHORIZATION.

NOTE: If objects in the schema on the current search path are with the same name, specify the schemas for different objects. You can run the SHOW SEARCH_PATH statement to check the schemas on the current search path.

Examples

• Create a schema for the user1 user.

  Create the user1 user.

  openGauss=# CREATE USER user1 IDENTIFIED BY 'XXXXXXXX';
  CREATE ROLE
  

  Create a schema based on the username.

  openGauss=# CREATE SCHEMA test AUTHORIZATION user1;
  CREATE SCHEMA
  

• Change the name of the test schema to test1.

  openGauss=# ALTER SCHEMA test RENAME TO test1;
  ALTER SCHEMA
  

• Change the owner of the schema.

  Create the user2 user.

  openGauss=# CREATE USER user2 IDENTIFIED BY 'XXXXXXXXX';
  CREATE ROLE
  

  Change the owner of the test1 schema to user2.

  openGauss=# ALTER SCHEMA test1  OWNER TO user2;
  ALTER SCHEMA
  

• Check the current search path.

  openGauss=# SHOW SEARCH_PATH;
    search_path
  ----------------
   "$user",public
  (1 row)
  

• Change the default schema of the current session.

  openGauss=# SET SEARCH_PATH TO test1, public;
  SET
  

• Delete the schema and its objects.

  openGauss=# DROP SCHEMA test1;
  DROP SCHEMA