PostgreSQL Schema

In 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 schema

Each 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

• Views
• Indexes
• Sequences
• Datatypes
• Operators
• Functions
• Tables
• Other relations.

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:

schema_name.object_name

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:

public.comany
Or
Employee.company

Why do we need to use Schemas in PostgreSQL?

In PostgreSQL, we are using the schemas for the following principal reasons:

• The end-users can use only those schemas that allow the separation of test tables and logic from production tables.
• In the schema, we can efficiently achieve the data and can be applied to some of the following activities:
  • We can quickly restore data, which is present in a distinct schema. Thus, the application-oriented schemas can be individually returned and backed up for recovery and time travel.
  • When the application data is in a schema, we can manage the application changes. Therefore, a new version of the application can work on the table structure in a new schema, including a simple modification to the schema name.
  • We can easily take the back-up of data present in a separate schema. And therefore, the unstable data can have several back-up plans from non-volatile data.

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 schema

We 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:

CREATE TABLE table_name;
And
CREATE TABLE public.table_name;

The PostgreSQL schema search path

• In PostgreSQL, we can search the table with the schema search path's help if we select a table only by its name.
• PostgreSQL will provide the privilege to the initial equivalent table in the schema search path. And if there is no match, it will throw an error, or even the name occurs in an alternative schema in the database.
• In PostgreSQL, the current schema is knowns as the primary schema in the search path.
• We can frequently refer to a table without a schema name in the schema search path.

For example

To access the Employee table, we use company.Employee.

Note:

• PostgreSQL will also use the current schema for the new object if we create a new object without describing a schema name.
• The current_schema() method is used to return the current schema.

SELECT current_schema();

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.

SHOW search_path;

Output

After executing the above command, we will get the below output:

In the above output:

• The "$user" defines the first schema that PostgreSQL will use to find the object with a similar name as the current user.
  For example
  If we use the Postgres user to login and have the privilege of the Employee table. In the Postgres schema, PostgreSQL will search for the Employee table. And it will remain to search for the object in the public schema if it cannot find any object like that.
• And the second component is specifying to the public schema, as we have understood before.

To create a new schema, we can use the CREATE SCHEMA command:

CREATE SCHEMA Company;

Output

And for adding the new schema to the search path, we can use the following statement:

SET search_path TO Company, public;

Output

If we create a new table Employee without describing the schema name, then PostgreSQL will put this Employee table into the Company schema:

CREATE TABLE Employee(
Emp_id SERIAL PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
Age integer NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
address CHARACTER(50), 
salary REAL
);

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:

SELECT * FROM Employee;
Or 
SELECT * FROM Company.Employee;

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:

SELECT * FROM public.Employee;

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 privileges

The 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:

GRANT USAGE ON SCHEMA schema_name TO user_name;

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:

GRANT CREATE ON SCHEMA schema_name TO user_name;

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.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Here, the first public refers to the schema, and the second public refers to every user.

PostgreSQL schema Actions

• We can use the CREATE SCHEMA command for creating a new schema.
• We can use the DROP SCHEMA command for dropping a schema.
• We can use the ALTER SCHEMA command for renaming a schema or modifying the owner.

Benefits of using PostgreSQL Schema

The 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:

• In PostgreSQL Schema, the critical advantage is that a database can have the same table name if it belongs to different schemas.
• The schemas can categorize the database objects into logical groups to make them more controllable.
• The schemas allow the users to use one database as an alternative of the multiple databases.
• To avoid collision with additional objects' names, we can place the third-party schemas into distinct schemas.
• The objects with similar name do not conflict as long as it is placed in different schemas.