openGauss---Schema

Schema
Schema is also called schema. By managing Schema, multiple users are allowed to use the same database without interfering with each other, database objects can be organized into logical groups that are easy to manage, and third-party applications can be easily added to the corresponding Schema without causing conflicts.

Each database contains one or more schemas. Each schema in a database contains tables and other types of objects. When the database is initially created, it has a schema named public by default, and all users have the permissions of this schema. Database objects can be grouped by Schema. Schemas are similar to operating system catalogs, but schemas cannot be nested.

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

When a user is created in the database, the system will automatically help the user to create a schema with the same name.

Database objects are created within the first schema in the database search path. See Search Paths for more information on the first schema case by default and how to change the schema order.

Create, modify and delete schemas
To create a Schema, use CREATE SCHEMA. By default, initial users and system administrators can create schemas. Other users need to have the CREATE permission of the database to create schemas in the database. For the authorization method, please refer to the grammar in Granting database access permissions to specified users or roles in GRANT.
To change the schema name or owner, use ALTER SCHEMA. The schema owner can change the schema.
To drop a schema and its objects, use DROP SCHEMA. Schema owners can delete schemas.

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

To see a list of all schemas, query the PG_NAMESPACE system table.

SELECT * FROM pg_namespace;

To view a list of tables belonging to a schema, query the system view PG_TABLES. For example, the following query returns a list of tables in Schema PG_CATALOG.

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

search path
The search path is defined in the search_path parameter, which takes a comma-separated list of schema names. If no target schema is specified when an object is created, the object is added to the first schema listed in the search path. When objects with the same name exist in different schemas, and the query object does not specify a schema, the object will be returned from the first schema that contains the object in the search path.

To see the current search path, use SHOW.
`postgres=# SHOW SEARCH_PATH;

search_path

"$user",public
(1 row)`

The default value of the search_path parameter is: "user", public. user represents the schema name with the same name as the current session username. If such a schema does not exist, $user will be ignored. So by default, after the user connects to the database, if there is a schema with the same name in the database, the object will be added to the schema with the same name, otherwise the object will be added to the Public Schema.

To change the default schema for the current session, use the SET command.

Execute the following command to set the search path to myschema, public, first search for myschema.

postgres=# SET SEARCH_PATH TO myschema, public;
SET