How to Drop Schema in PostgreSQL Database Server

By Posted on

Introduction

This is an article where the main focus is just to be able to drop a specific schema. In other words, the schema which exist in PostgreSQL database server. Normally, in a PostgreSQL database, there is one default schema exist. That one default schema is a schema with the name of ‘public’. On the other hand, there is a possibility for a PostgreSQL database server to have another schema. So, this article will show how to drop the schema in PostgreSQL database server.

How to Drop Schema in PostgreSQL Database Server

Basically, it is possible to drop a certain schema which in this context it is a non-public schema in a PostgreSQL database server. It actually need a certain query command in order to do that. Just execute that certain query command in the PostgreSQL command console. If there is no problem with the query command, the schema will be deleted. Below are the steps for removing a certain schema exist in the PostgreSQL database server :

  1. First of all, make sure that PostgreSQL database server is running. If there is no PostgreSQL database server running, just install it first. Make sure to start the PostgreSQL database server after installing it.

  2. Next, try to access the PostgreSQL command console. It exist as in the following appearance : 

    Microsoft Windows [Version 10.0.22000.1219]
(c) Microsoft Corporation. All rights reserved.

C:\Users\Personal>psql -Upostgres
Password for user postgres:
psql (14.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

  3. Then, connect to the database where the schema exist which in this context, it is a database with the name of ‘mydb’. So, execute it in the PostgreSQL command console : 

    postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#

  4. After that, list all of the available schema in the database by typing the following command : 

    mydb=# \dn *
List of schemas
Name                | Owner
--------------------+----------
asset               | postgres
information_schema  | postgres
report              | postgres
pg_catalog          | postgres
pg_toast            | postgres
product             | postgres
public              | postgres
test                | postgres
(8 rows)

mydb=#

  5. Suppose, there is a requirement in order to drop the schema, for an example the schema with the name of ‘test’, just execute the command with pattern as follows : 

    mydb=# drop schema if exists test cascade;
DROP SCHEMA
mydb=#

  6. Check again whether the schema has already been perfectly deleted by executing the command once more : 

    mydb=# \dn *
List of schemas
Name                | Owner
--------------------+----------
asset               | postgres
information_schema  | postgres
report              | postgres
pg_catalog          | postgres
pg_toast            | postgres
product             | postgres
public              | postgres
(7 rows)

mydb=#

Leave a Reply