PostgreSQL learning Manual (ii) Schema)

A database contains one or more naming modes. The schema also contains tables. The schema also contains other named objects, including data types, functions, and operators. The same object name can be used in different modes without causing conflicts. For example, both schema1 and myschema can contain tables called mytable. Unlike databases, the mode is not strictly separated: a user can access objects in any mode in the database connected to him, as long as he has permissions.
There are several main reasons for this pattern:
1). Allow multiple users to use one database without interfering with other users.
2) Organize database objects into logical groups for easier management.
3) third-party applications can be placed in different modes so that they do not conflict with the names of other objects.

1. Creation Mode:
Create SchemaMyschema;
You can use the preceding command to create a schema named myschema. After the schema is created, it can have its own logical objects, such as tables, views, and functions.

2. Public mode:
Before introducing the following content, we need to explain the public mode first. Every time we create a new database, PostgreSQL will automatically create this mode for us. When you log on to the database, if there is no special designation, we will operate a variety of data objects in this mode (public), such:
Create TableProducts (...) Equivalent Create TablePublic. Products (...)

3. Permissions:
Lack of time-saving, users do not see all their objects in the mode. To make them visible, the mode owner must grant the usage permission to the mode. To allow users to use objects in the mode, we may need to grant additional permissions, as long as they are suitable for this object. PostgreSQL provides different permission types based on different objects, such:
Grant all on SchemaMyschemaToPublic;
The above all keyword will contain Create And Usage Two permissions. If the public mode has the create permission in myschema mode, users logging on to this mode can create any object in myschema mode, for example:
Create TableMyschema. products (
Product_no integer,
Name text,
Price numericCheck(Price> 0 ),
);
When granting permissions to all tables in the mode, you need to split the permissions into different table operations, such:
Alter default privileges in SchemaMyschema
Grant insert, select, update, delete, truncate, references, trigger on tables ToPublic;
When granting permissions to all sequence objects in the mode, you need to split the permissions into different sequence operations, such:
Alter default privileges in SchemaMyschema
Grant select, update, usage on sequencesPublic;
When granting permissions to all functions in the mode, only execution permissions are considered. For example:
Alter default privileges in SchemaMyschema
Grant execute on functionsPublic;
It can be seen that it is extremely inconvenient to create various objects in myschema mode in public mode. Next we will introduce another method, that is, using the role object to directly log on and associate it with the myschema object. Then we can directly create various required objects in myschema mode.
Create roleMyschemaLogin Password'123 '; -- Created a role object associated with this mode.
Create SchemaMyschemaAuthorizationMyschema; -- Associate the mode with the specified role. The mode name and role name can be different.
In Linux Shell, log on to the database mytest as the role of myschema. After the password is entered correctly, the database is successfully logged on.
/> Psql-D mytest-u myschema
Password:
Mytest => Create Table Test (I integer );
Create Table
Mytest => \ D -- View the list of tables information that can be viewed in this mode.
List of relations
Schema | Name | type | Owner
------------ + --------- + ------ + ----------
Myschema | test | table | myschema
(1 rows)

4. Deletion mode:
Drop SchemaMyschema;
To delete the mode and all its objects, use cascading deletion:
Drop SchemaMyschemaCascade;

5. Mode search path:

When using a database object, we can use its full name to locate the object. However, this is often very cumbersome and we have to type
Owner_name.object_name. PostgreSQL provides the mode search path, which is similar to the $ PATH environment variable in Linux. When we execute a shell command, only the command is in the $ PATH directory list, you can run the command directly by using the command name. Otherwise, you need to enter the full path name. PostgreSQL also finds a search path to determine which table a table is. This path is a list of modes to be searched. The first table found in the search path is treated as the selected table. If no matching table exists in the search path, an error is reported, even if the name of the matching table exists in other database modes.
The first mode in the search path is the current mode. In addition to the first mode of the search, it also creates the mode of the table when the create table does not declare the mode name. To display the current search path, run the following command:
Mytest => Show search_path;
Search_path
----------------
"$ User", public
(1 row)
You can add the new mode to the search path, for example:
Set search_pathMyschema, public;
Set the specified mode for the search path, for example:
Set search_pathMyschema; -- The current search path will only contain the myschema mode.

 

 

 

 

 

Reprinted from Stephen Liu for the purposes of learning to add to favorites only.