A PostgreSQL schema can be defined as a named collection of the tables inside that schema. Views, indexes, sequences, data types, operators, and functions can also be included in a particular schema. By default, each database has one public schema.
This is how a schema looks like in a pgAdmin tool. All the objects that are present inside a schema are clearly visible in the image.
Benefits of using PostgreSQL Schema
If there are multiple users, we can use PostgreSQL Schemas to allow different users to use a single database. Here are some of the major advantages of PostgreSQL schema:
- With the help of PostgreSQL Schema, we can create two different tables with the same names within the same database because both will be in different schemas.
- The database can be divided into logical divisions with the use of PostgreSQL for different users.
- The schemas allow the users to use one database as an alternative to the multiple databases.
- The third-party schemas can be placed into distinct schemas to avoid collision with additional objects names.
- The objects having similar name will not conflict with each other as long as it is placed in different schemas.
How to create a Schema in PostgreSQL?
The command to create a schema inside a PostgreSQL database is,
CREATE SCHEMA name;
where the name can be any name of the schema that you want to give to your schema.
After the creation of a schema “Create Schema”, message is displayed.
To list all the schemas present in a particular database, use the command ‘\dn’
Alternatively, we can also use SQL query to list all the schemas that are present in all the databases.
SELECT schema_name FROM information_schema.schemata;
Create a table in a particular schema
To create a table inside a particular schema, the syntax is:
CREATE TABLE name_of_schema.name_of_table ( <columns> );
As shown in the image, the table named ‘person’ having a column named ‘name’ is created inside the schema named ‘newschema’.
How to drop a PostgreSQL Schema?
There are two ways to remove or drop a PostgreSQL schema. The first one by PostgreSQL CLI,i.e.,psql and the other way is using the PostgreSQL management tool pgAdmin4.
First, we’ll see how to do it with the help of psql. The syntax to drop a schema is:
DROP SCHEMA schema_name;
DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];
We get an error if we directly try to remove a schema and that schema has a table that has records stored within it.
So, in order to delete a schema having a table storing the records within, we have to use the ‘Cascade’ option, which will explicitly delete the schema while having the table storing the records or tuples within.
After using the ‘Cascade’ option, the query gets executed successfully without any error generation.
Now, we will how to delete a schema using the PostgreSQL management tool, pgAdmin4.
To drop a schema, select the schema that you want to drop and click on the ‘Delete/Drop’ option from the drop-down list. And then click ‘Yes’.
An error message is displayed because the selected schema must have some table within it.
To delete a schema having some table, use the ‘Drop Cascade’ option instead of the ‘Delete/Drop’ option.
After using this option, the schema will get successfully dropped.
How to alter a PostgreSQL Schema?
We can alter a PostgreSQL schema using the “ALTER SCHEMA” command. We change the name of the schema and change the owner of the schema inside a particular database.
To rename a schema the command is:
ALTER SCHEMA schema_name RENAME TO new_name;
Here the schema_name will be replaced with the name of your schema and the new_name will be replaced with the new name that you want to give to your schema.
As shown in the example, the schema named ‘newschema’ is renamed to ‘myschema’ using the ‘ALTER SCHEMA’ command having the syntax,
ALTER SCHEMA newschema RENAME TO myschema;
And to change the owner of the schema, use the command ‘ALTER SCHEMA’ having syntax,
ALTER SCHEMA schema_name OWNER TO username;
Here the schema_name will be replaced with the name of the schema and the username will be replaced with the name of the user to whom the access is to be granted.
So, with the help of this article, we are able to understand the concept of the schemas and their need in the PostgreSQL database.