Schemas

Schemas function as models. Schemas allow multiple users to use the same database without interference. In this way, database objects can be organized into logical groups that are easy to manage, and third-party applications can be added to corresponding schemas without causing conflicts.

Each database has one or more schemas. Each schema contains tables and other types of objects. When a database is created, a public schema named public is created by default, and all users have the USAGE permission on this schema. In addition, each database has a pg_catalog schema, which contains system catalogs and all built-in data types, functions, and operators. Only system administrators and initial users can create common functions, aggregate functions, stored procedures, and synonym objects in public and pg_catalog schemas. Only initial users can create operators in public and pg_catalog schemas. Other users cannot create the preceding five types of objects even if they are granted the CREATE permission on the public and pg_catalog schemas. You can group database objects by schema. A schema is similar to an OS directory but cannot be nested. By default, only the initial user can create objects in pg_catalog.

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 run the CREATE USER command to create a user, the system creates a schema with the same name as the user in the database where the command is executed.

Database objects are generally created in the first schema in a database search path. For details about the first schema and how to change the schema order, see Search Path.

Creating, Modifying, and Deleting Schemas

Create a schema. For details, see CREATE SCHEMA. By default, the initial user and system administrator can create schemas. Other users can create schemas in the database only when they have the CREATE permission on the database. For details about how to grant the permission, see the syntax in GRANT.
To change the name or owner of a schema, use ALTER SCHEMA. The schema owner can change a schema.
To delete a schema and its objects, use DROP SCHEMA. The schema owner can delete a schema.
To create a table in a schema, use the schema_name.table_name format to specify the table. If schema_name is not specified, the table will be created in the first schema in Search Path.

To view the owner of a schema, perform the following join query on the system catalogs PG_NAMESPACE and PG_USER. Replace schema_name in the statement with the name of the schema to be queried.

      
           gaussdb=# SELECT s.nspname,u.usename AS nspowner FROM pg_namespace s, pg_user u WHERE nspname='schema_name' AND s.nspowner = u.usesysid;

To view a list of all schemas, query the system catalog PG_NAMESPACE.
1

gaussdb=# SELECT * FROM pg_namespace;

To view a list of tables in a schema, query the system catalog PG_TABLES. For example, the following query will return a table list from PG_CATALOG in the schema.

      
           gaussdb=# SELECT distinct(tablename),schemaname from pg_tables where schemaname = 'pg_catalog';

Search Path

A search path is defined in the search_path parameter. The parameter value is a list of schema names separated by commas (,). If no target schema is specified during object creation, the object will be added to the first schema listed in the search path. If there are objects with the same name across different schemas and no schema is specified for an object query, the object will be returned from the first schema containing the object in the search path.

To view the current search path, use SHOW.

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

The default value of search_path is "$user",public. $user indicates the name of the schema with the same name as the current session user. If the schema does not exist, $user will be ignored. By default, after a user connects to a database that has schemas with the same name, objects will be added to all the schemas. If there are no such schemas, objects will be added only to the public schema.

To change the default schema of the current session, run the SET statement.
Set the search path to myschema, public (myschema will be searched first).
1 2

gaussdb=# SET SEARCH_PATH TO myschema, public; SET