The PostgreSQL Schema is essentially a namespace,which contains named objects like tables, data types, functions, and operators and a database can contain any number of PostgreSQL Schema. Each PostgreSQL Schema should be unique and different from each other. And each schema consisting of different number of objects. The main advantage of this PostgreSQL Schema is that a database can have same table name as long as it belongs to different schemas.
Creating two schemas with same table names employee is as follows:
Step - 1
In PostgreSQL 9.6, under SQLDB database select schema.
Step - 2
Right click on schema and select New Schema.
Step - 3
Assign the new schema name as Selection and click on OK button.
Step - 4
The selection schema will be appeared in the main schemas collections.
Step - 5
Again right click on schema and select New schema.
Step - 6
Assign the new schema name as criteria.
Step - 7
Then both the newly created schema will be appeared in the main schema.
Step - 8
In the criteria schema, right click on table and select New table.
Step - 9
And assign the table name as Employee, and select columns.
Step - 10
It display a new page,under it assign the first column name as emp_id along with datatype and click on OK button.
Step - 11
Here the first column name emp_id as be successfully created. And click on Add button.
Step - 12
Assign the second column name as Emp_Name along with the datatype and click on OK button.
Step - 13
Assign the third column name as Age along with the datatype and click on OK button.
Step - 14
All the 3 column names along with datatypes are shown below. Click on Add for adding any new columns or else click on OK button.
Step - 15
Select table under criteria.
Step - 16
Right click on table and select script followed by Create Script.
Step - 17
For Criteria schema create table employee has been displayed.
Step - 18
Copy the employee create table.
Step - 19
In criteria schema employee table has been successfully created.
Step - 20
Under selection schema select table and click on SQL execute query.
Step - 21
Select SQL Editor from the appeared window.
Step - 22
Then it will automatically paste the criteria employee table.
Step - 23
Rename from criteria to selection and click on execute query. It display the query was returned successful.
Step - 24
Then in selection schema employee table has been successfully created.
Step - 25
Here in both the schemas employee tables have been created.
The advantages of schemas include:
- Schemas allows users to use one database instead of multiple database.
- Schemas organizes database objects into logical groups.
- Objects with same name does not conflicts if placed in different schemas.
- PostgreSQL Schema – Is a namespace that contains named objects.