Learn with Yasir

Share Your Feedback

Fix 'psql' is not recognized on Windows


Solutions to fix the 'psql' is not recognized error on Windows by adding PostgreSQL to PATH or reinstalling PostgreSQL with psql.

Deleting a Database in PostgreSQL

Student Notes Database Management Systems (DBMS)

1. Introduction

Sometimes, we create databases for testing or practice that are no longer needed. PostgreSQL provides the DROP DATABASE command to permanently delete an existing database.

Example:

Suppose we have a database named college. We no longer need this database and want to remove it from PostgreSQL.

We can use the DROP DATABASE command.

Remember: Deleting a database removes the database and all the tables, records, and other objects stored inside it.

2. What is DROP DATABASE?

DROP DATABASE is an SQL command used to permanently delete an existing database from a PostgreSQL server.

In simple words:

It tells PostgreSQL to remove a complete database and its contents.

3. SQL Syntax

The general syntax for deleting a database is:

DROP DATABASE database_name;

Explanation:

Part Description
DROP Removes an existing database.
DATABASE Specifies the object we want to delete.
database_name Name of the database to delete.
; Ends the SQL statement.

4. Practical Example

Suppose we have an existing database named college.

We want to delete it.

SQL Command:

DROP DATABASE college;

Explanation:

  1. DROP tells PostgreSQL to remove something.
  2. DATABASE specifies that we want to delete a database.
  3. college is the name of the database we want to delete.
  4. ; indicates the end of the SQL statement.

Expected Result

DROP DATABASE

PostgreSQL displays this message when the database is successfully deleted.

5. Important Conditions Before Deleting a Database

PostgreSQL has some important restrictions when deleting a database.

  1. You must have permission to delete the database.
  2. You cannot delete the database to which you are currently connected.
  3. Active connections to the database can prevent its deletion.
  4. Once deleted, the database and its contents are no longer available through PostgreSQL.
  5. Always verify the database name before executing the command.

Warning

DROP DATABASE is a destructive operation. It permanently removes the database and its contents. Take a backup before deleting important databases.

6. Deleting a Database Using pgAdmin

We can also delete a database using pgAdmin.

Follow these steps:

How to drop a database in PostgreSQL — CommandPrompt Inc.

commandprompt.com

  1. Open pgAdmin.
  2. Connect to your PostgreSQL server.
  3. Expand the Databases section.
  4. Right-click the database you want to delete.
  5. Select Delete / Drop.

【pgAdmin4】创建/删除:数据库Database和数据库表Table-CSDN博客

blog.csdn.net

  1. Review the database name.
  2. Confirm the deletion.

The selected database will be removed from the Databases list.

7. Deleting a Database Using SQL in pgAdmin

We can also delete a database through the Query Tool.

Step 1: Connect to another database

For example, select the postgres database and open its Query Tool.

Step 2: Execute the SQL command

DROP DATABASE college;

Step 3: Verify the result

Execute the following query:

SELECT datname
FROM pg_database;

The database college should no longer appear in the result.

8. Using IF EXISTS

Sometimes, we may try to delete a database that does not exist.

PostgreSQL will generate an error.

To avoid this error, we can use IF EXISTS.

SQL Command:

DROP DATABASE IF EXISTS college;

Explanation:

  • DROP DATABASE deletes the database.
  • IF EXISTS checks whether the database exists.
  • college is the database name.

If the database does not exist, PostgreSQL displays a notice instead of generating an error.

Remember: IF EXISTS does not protect an existing database from deletion. If it exists, PostgreSQL will delete it.

9. Using WITH (FORCE)

PostgreSQL also provides an option to terminate eligible connections before deleting a database.

DROP DATABASE college WITH (FORCE);

Explanation:

  • WITH (FORCE) attempts to terminate existing connections.
  • PostgreSQL then attempts to delete the database.

Note: This requires appropriate privileges and may fail if some connections cannot be terminated or if the database has other blocking conditions. Beginners should normally close connections manually first.

10. Common Errors

Error Reason Solution
Cannot drop the currently open database Connected to the target database Connect to postgres first
Database is being accessed by other users Active connections exist Close connections or use WITH (FORCE) where appropriate
Database does not exist Incorrect database name Verify the database name
Permission denied Insufficient privileges Use an authorized database owner or administrator

11. Practice Exercises

Exercise 1: Write SQL Commands

Write SQL commands to perform the following tasks:

  1. Delete a database named school.
  2. Delete a database named college.
  3. Delete a database named student_db using IF EXISTS.
  4. Delete a database named library using WITH (FORCE).

Exercise 2: Practical Activity

Perform the following tasks using pgAdmin:

  1. Create a database named practice_db.
  2. Create another database named test_db.
  3. Verify that both databases exist.
  4. Delete test_db using pgAdmin.
  5. Delete practice_db using an SQL command.
  6. Verify that both databases have been deleted.

12. Review Questions

  1. Which SQL command is used to delete a database?
  2. What happens when we execute DROP DATABASE?
  3. Can we delete a database while connected to it?
  4. What is the purpose of IF EXISTS?
  5. What is the purpose of WITH (FORCE)?
  6. Why should we take a backup before deleting a database?
  7. How can we verify that a database has been deleted?

13. Quick Revision

Task SQL Command
Create database CREATE DATABASE college;
Rename database ALTER DATABASE college RENAME TO university;
Delete database DROP DATABASE college;
Delete if exists DROP DATABASE IF EXISTS college;
Delete with force DROP DATABASE college WITH (FORCE);
List databases SELECT datname FROM pg_database;

Key Takeaway: The DROP DATABASE command permanently removes an existing database and all its contents. Always check the database name and take a backup before executing this command.