PostgreSQL SchemaIn this section, we are going to learn PostgreSQL Schema, the public Schema, how PostgreSQL uses the schema search path to resolve objects in Schema, PostgreSQL schema and privileges, and the advantages of using PostgreSQL Schema. Introduction of PostgreSQL schemaEach database starts with one schema, and it should be the public schema. And a schema is a named collection of tables. The Schema is a namespace which provides several objects such as
Note: The PostgreSQL Schema is a namespace that holds named objects.In PostgreSQL schema, we can fix the name with the schema name as a prefix for accessing an object of a schema with the help of the below statement: Otherwise, we can set a search path that contains the schema, and we can also cover the schema search path in the future. A database can have one or more schemas, whereas each schema exists for only one database, and two schemas can contain different objects, which share a similar name. For example Suppose we have Company schema which contains the Employee table, and the public schema also has the Employee table. When we refer to the Employee table, it should be as follows: Why do we need to use Schemas in PostgreSQL?In PostgreSQL, we are using the schemas for the following principal reasons:
Thus, for creation purposes, we can keep our application data separate in schemas advance management. And for the end-user purposes, we can keep our users in different schemas by stepping on each other. The public schemaWe created tables without describing any schema names in the earlier topics of the PostgreSQL tutorial. Those tables and additional objects are automatically placed into the public schema by default. For every new database, PostgreSQL generates a schema called the public. Hence, the following commands are parallel: The PostgreSQL schema search path
For example To access the Employee table, we use company.Employee. Note:
Output After executing the above command, we will get the below output: Note: PostgreSQL uses the public for every new object that we create in the database.We can use the below SHOW statement to view the current search path. Output After executing the above command, we will get the below output: In the above output:
To create a new schema, we can use the CREATE SCHEMA command: Output And for adding the new schema to the search path, we can use the following statement: Output If we create a new table Employee without describing the schema name, then PostgreSQL will put this Employee table into the Company schema: Output The below screenshot displays the new schema Company and the Employee table, which belongs to the Company schema: For accessing the Employee table in the Company schema, we can use one of the below commands: The public schema is the second component in the search path; therefore, to access the Employee table in the public schema, we should specify the table name below: We will get the below outputs, once we execute all the above statements. After running the first command, we will get the below output: After executing the second command, we will get the below output: Once we execute the last command, we get the below output: After performing all the three commands, we will get the same output, that's why we can use any of the above query to access the employee table in the Company schema. Note: We can drop the public schema as well because it is not a unique schema.PostgreSQL schemas and privilegesThe users can only have the privilege to access the objects in the schemas that they created, which implies that the user cannot use any other object in the schemas that are not created by them. So, we must grant the usage privilege for the users to get all the access of the objects in the schema, which they did not create. The below command is used to get the usage privilege for the users on the schema: And to permit the users to create an object in the schema that they do not create, we must grant them the CREATE privilege. The below command is used to grant the create privilege on the schema: Note: Each user has the USAGE and CREATE privileges on the public schema by default.We can revoke that privilege if we do not want to access that with the below command's help. Here, the first public refers to the schema, and the second public refers to every user. PostgreSQL schema Actions
Benefits of using PostgreSQL SchemaThe schema allows us to simplify many users for using one database without involving each other. Some of the prevalent advantages of PostgreSQL schema are as follows:
Next TopicPostgreSQL Create Schema |