openGauss Schemas
Tags: opengauss
Published: 2023-03-21 02:29:19

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 initially created, it has a public schema by default, and all users have the usage permission on the schema. Only the system administrator and initial users can create common functions, aggregate functions, stored procedures, and synonym objects in the public schema. Only the initial users can create operators in the public schema. Other users cannot create the preceding five types of objects even if they are granted the create permission. 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 under the pg_catalog schema.

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
To create a schema, use 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 the schema.

To delete a schema and its objects, use DROP SCHEMA. Schema owners can delete schemas.

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.

""
openGauss=# 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.

""
openGauss=# 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.

""
openGauss=# 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 is 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.

""
openGauss=# 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.

Run the following command to set search_path to myschema and public (myschema will be searched first):

""
openGauss=# SET SEARCH_PATH TO myschema, public;
SET