Solutions to fix the 'psql' is not recognized error on Windows by adding PostgreSQL to PATH or reinstalling PostgreSQL with psql.
| Student Notes | Database Management Systems (DBMS) |
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.
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.
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. |
Suppose we have an existing database named college.
We want to delete it.
SQL Command:
DROP DATABASE college;
Explanation:
DROP tells PostgreSQL to remove something.DATABASE specifies that we want to delete a database.college is the name of the database we want to delete.; indicates the end of the SQL statement.Expected Result
DROP DATABASE
PostgreSQL displays this message when the database is successfully deleted.
PostgreSQL has some important restrictions when deleting a database.
Warning
DROP DATABASE is a destructive operation. It permanently removes the database and its contents. Take a backup before deleting important databases.
We can also delete a database using pgAdmin.
Follow these steps:
How to drop a database in PostgreSQL — CommandPrompt Inc.
【pgAdmin4】创建/删除:数据库Database和数据库表Table-CSDN博客
The selected database will be removed from the Databases list.
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.
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.
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.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.
| 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 |
Write SQL commands to perform the following tasks:
school.college.student_db using IF EXISTS.library using WITH (FORCE).Perform the following tasks using pgAdmin:
practice_db.test_db.test_db using pgAdmin.practice_db using an SQL command.DROP DATABASE?IF EXISTS?WITH (FORCE)?| 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.