PostgreSQL Create And Drop Schema

PostgreSQL - Create And Drop User

PostgreSQL - Privileges

PostgreSQL Schema is the collection of objects such as tables, functions, procedures, and data types. Schema can be created by using the CREATE SCHEMA command and once database can have multiple schemas.

Creating Schema provides many benefits and a few of them are mentioned below.

• Schema helps users to use one database without interrupting anyone.
• Schema separates the third-party application schema so that they don't overlap with another schema.
• Schema helps to manage database objects in an organized way.

Now let us see how to create and drop the PostgreSQL Schema in the following section.

1. Create PostgreSQL Schema

To create PostgreSQL Schema, we can use the CREATE SCHEMA command.

In the below section we will create a schema name CLOUDDUGGU_SCHEMA. Once the Schema is created use the \dn command to display all schema present in the PostgreSQL.

Command:

   postgresql=# CREATE SCHEMA CLOUDDUGGU_SCHEMA;

   postgresql=# \dn

Output:

2. Create PostgreSQL Schema Similar to Username

We can create a Schema similar to the username. In the following section, we have created a schema named WASI that is similar to the username WASI. Once the schema is created we can verify it using the psql \dn command.

Command:

   postgresql=# CREATE SCHEMA AUTHORIZATION WASI;

   postgresql=# \dn

Output:

3. Create PostgreSQL Schema And Assign It To Username

In this section, we will create a new schema named emp_records and assign it to username WASI.

Command:

   postgresql=# CREATE SCHEMA IF NOT EXISTS EMP_RECORDS AUTHORIZATION WASI;

   postgresql=# \dn

Output:

4. Drop PostgreSQL Schema

PostgreSQL Schema can be dropped using the DROP SCHEMA command. In case there are objects associated with schema then we won't be able to drop it.

Let us drop the cloudduggu_schema schema using in the below section.

Command:

   postgresql=# \dn

   postgresql=# DROP SCHEMA CLOUDDUGGU_SCHEMA;

   postgresql=# \dn

Output:

Command:

To drop all objects of the Schema we can use the CASCADE keyword as mentioned in the below example.

   postgresql=# DROP SCHEMA CLOUDDUGGU_SCHEMA CASCADE;

   postgresql=# \dn

Output: