Updated on 2025-09-22 GMT+08:00

Schemas

Overview

A schema in a database is an object used to organize tables, views, indexes, functions, operators, and other objects. Its functions include:

  • Logical isolation: Database objects are organized into manageable logical groups. Different schemas can contain objects with the same name without conflicts.
  • Permission control: Schemas enable more precise control over users' access to databases. Permissions can be granted or revoked at the schema level to ensure data security and isolation.
  • Easy management: Schemas enhance the convenience and flexibility of managing database objects, especially in the multi-application and multi-user environments.

Creating and Managing Schemas

The following describes only some common syntax. For details, see CREATE SCHEMA, ALTER SCHEMA, and DROP SCHEMA.

  • Creating a schema
    gaussdb=#CREATE SCHEMA sch1 ;
    CREATE SCHEMA
    
    -- View the schema information.
    gaussdb=#\dn sch1
    List of schemas
     Name | Owner 
    ------+-------
     sch1 | omm
    (1 row)
  • Modifying a schema
    -- Create a user.
    gaussdb=#CREATE USER u1 PASSWORD '********';
    CREATE ROLE
    
    -- Rename a schema.
    gaussdb=#ALTER SCHEMA sch1 RENAME TO sch2;
    ALTER SCHEMA
    
    -- Change the owner of a schema.
    gaussdb=#ALTER SCHEMA sch2 OWNER TO u1;
    ALTER SCHEMA
    
    -- View the schema information.
    gaussdb=#\dn sch*
    List of schemas
     Name | Owner 
    ------+-------
     sch2 | u1
    (1 row)
  • Dropping a schema
    gaussdb=#DROP SCHEMA sch2;
    DROP SCHEMA
    
    -- Drop a user.
    gaussdb=#DROP USER u1;
    DROP ROLE

Search Path

A search path is used to determine the sequence in which the database searches for objects in the corresponding schema when parsing object names. The setting of the search path can affect the analysis and execution of SQL queries. When a user uses an object (such as a table, view, index, or function) without specifying a schema, the database searches for the object in the defined search path sequence until the object is found.

  • Query the search path.
    In the following example, if no schema name is specified, the database searches for objects in the schema with the same name as the user and then in the public schema.
    -- Query the current search path.
    gaussdb=#SHOW search_path;
      search_path   
    ----------------
     "$user",public
    (1 row)
  • Set the search path.
    • The search path setting takes effect only in the current session and does not affect other sessions.
    • If no schema is specified when an object is created, the object is created in the first schema in the search path by default.
    -- Create a schema.
    gaussdb=#CREATE SCHEMA sch1;
    CREATE SCHEMA
    gaussdb=#CREATE SCHEMA sch2;
    CREATE SCHEMA
    
    -- Create tables with the same name in two schemas and insert data into the tables.
    gaussdb=#CREATE TABLE sch1.tb_test(c1 int);
    CREATE TABLE
    gaussdb=#INSERT INTO sch1.tb_test VALUES (1);
    INSERT 0 1
    gaussdb=#CREATE TABLE sch2.tb_test(c1 int);
    CREATE TABLE
    gaussdb=#INSERT INTO sch2.tb_test VALUES (2);
    INSERT 0 1
    
    -- Set the sequence of the search path sch1 to the front, and then view the table data.
    gaussdb=#SET search_path = sch1,sch2,public;
    SET
    gaussdb=#SELECT * FROM tb_test;
     c1 
    ----
      1
    (1 row)
    
    
    -- Set the sequence of the multi-path sch2 to the front, and then view the table data.
    gaussdb=#SET search_path = sch2,sch1,public;
    SET
    gaussdb=#SELECT * FROM tb_test;
     c1 
    ----
      2
    (1 row)
    
    -- Reset search_path and drop schemas and tables.
    gaussdb=#RESET search_path;
    RESET
    gaussdb=#DROP TABLE sch1.tb_test;
    DROP TABLE
    gaussdb=#DROP TABLE sch2.tb_test;
    DROP TABLE
    gaussdb=#DROP SCHEMA sch1;
    DROP SCHEMA
    gaussdb=#DROP SCHEMA sch2;
    DROP SCHEMA