A schema is a named collection of tables. It can also contain views, indexes, sequences, datatypes, operators and functions.
In PostgreSQL, The CREATE SCHEMA statement is used to create a schema. Schemas cannot be nested.
Create schema with PostgreSQL UI:
Follow the following steps to create a schema:
- Open pgAdmin and connect PostgreSQL to the local host server.
- Expand the databases by clicking on the plus icon.
- You can see that there are three databases. Here, we are taking the first database javatpoint
- Expand the database "javatpoint".
- Here, you can see "schemas". Select schemas and make a right click on it. You can see New schema. Click on it and create a new schema.
- A schema is created named "myschema".
PostgreSQL Create Table in Schema
Follow the following steps to create a table in schema:
- Expand the newly created schema "myschema".
- You can see the following.
- Select table and make a right click on that. You can see "New table". Click on the new table and create the table.
- You can add columns by click on the columns and add buttons.
Drop PostgreSQL Schema
You can drop the schema if you don't need it anymore.
Follow the below instructions to drop or delete the schema:
- Click on the schema. You will see the following page.
- Select "myschema" and make a right click on it. You see delete/drop option. Click on the delete/drop option.
- Click on the "Yes" button. If it show the following box.
- Click ok if you see the following box.
- Then, drop the dependent object first.
- Right click on the "myschema". You will see the "Drop cascaded", click on it.
- Click "Yes" to delete the dependent objects.
- The schema is automatically deleted after deleting the dependent objects.
Advantages of using a Schema:
- Schema facilitates many users to use one database without interfering with each other.
- It organizes database objects into logical groups to make them more manageable.
- You can put the third party schemas into separate schemas to avoid collision with the names of other objects.