Introduction to PostgreSQL DROP DATABASE
PostgreSQL drop database statement is used to drop the database, we can drop the unwanted database from the server using drop database command in PostgreSQL. We can use database name with drop database command which was we want to drop from the database server, this statement will delete or drop the catalog entries and physical data directory files from the server. We cannot undo the statement of the drop database, so we need to fire this command with caution. To execute this command on PostgreSQL we have the permission of superuser or admin user, another user cannot execute this command.
Syntax and Parameters
Below is the syntax :
- Drop database database_name (Name of database which was we have drop from the database server);
OR
- Drop database [ IF EXISTS ] database_name (Name of database which was we have drop from the database server);
OR
- Dropdb [options (We have used multiple options while dropping database in PostgreSQL)] database_name.
Below is the parameter description syntax of the drop database statements in PostgreSQL.
- Drop: Drop database statement in PostgreSQL basically used to drop the unused database from the server.
- Database name: This is defined as the name of the database which was we have dropped from the database server.
- If exists: This is defined as dropping the database using if exists options, after using this option if the database does not exist on database server it will not show the error.
- Dropdb: We can drop the database from the command line using dropdb command. We can drop the database using dropdb command in PostgreSQL.
- Options: This is defined as we have used multiple options with dropdb command. We have using multiple options while dropping database in PostgreSQL.
We have using below options while dropping the database using dropdb command.
- –V: This option is used while we have printing the version of dropdb.
- –i: It will display the verification prompt before performing the command of dropdb.
- –help: It will display the dropdb command.
- –h: It is defined as drop the database as specified host.
- –p: It is defined as drop the database as a specified port.
- –U: This is defined as use the username to drop the database.
- –W: This is defined as use the username password to drop the specified database.
How DROP DATABASE statement work in PostgreSQL?
- Below is the working of the drop database statement in PostgreSQL. We can drop the database using the drop database and dropdb command in PostgreSQL.
- To execute the command of drop database in PostgreSQL we need to privileges database owners or privileges of admin privileges.
- This will issue the error like “ERROR: must be the owner of the database drop_test”. While dropping the drop_test database.
Code: Below example shows that we need to have privileges of database owner or admin user in PostgreSQL.
psql -U db_test -d postgres
drop database drop_test;
psql -U postgres
drop database drop_test;
Output:
- In above first example, we have tried to drop the database using the db_test user, it will issue the error that we cannot drop the database because it will not owner of the database name of drop_test.
- In second example we have drop the database using Postgres user, it will not shows the error because it is an admin user.
- We cannot rollback the transaction of drop database in PostgreSQL, so we need to use this statement with caution.
Code: Below example states that we cannot rollback the transaction of drop database in PostgreSQL.
\set AUTOCOMMIT OFF
\echo :AUTOCOMMIT
drop database drop_db;
rollback;
\l+
Output:
- In above example we have dropped the database and trying to roll back the same but it will not rollback the transaction of the drop database.
- We can drop the database using command line as well as database shell prompt.
- We are using drop database statements using shell prompt and dropdb command for drop the database from shell prompt.
- If we have permission of drop database we can drop the database using GUI tools like pg_admin.
Examples to Implement PostgreSQL DROP DATABASE
Below are the examples mentioned:
1. Drop the database without using any parameter
- In below example we have not used any parameter to drop the database. We have dropping the database from the database prompt.
- We have drop the database name as a db_test from the user postgres. Postgres user have admin privileges.
Code:
\l+
drop database db_test;
\l+
Output:
2. Drop the database using if exists parameter
- In below example we have used if exist parameter to drop the database. We have dropping the database from the database prompt in PostgreSQL.
- We have drop the database name of db_test1 from the user postgres.
Code:
\l+
drop database if exists db_test1;
\l+
drop database if exists db_test1;
Output:
3. Drop the database from command line prompt
- In below example we have drop the database from the command line prompt. We have drop the database from the command line prompt in PostgreSQL.
- We have drop the database name of db_test2 from the user postgres.
Code:
psql -U postgres -c "drop database db_test2"
psql -U postgres
\l+
Output:
4. Drop the database using dropdb command
In below example we have drop the database using dropdb command. We have drop the database from the command line prompt using dropdb command.
Code:
dropdb -U postgres db_test3
psql -U postgres
\l+
Output:
Conclusion
Drop database statement basically used to drop the existing or unused database from the database server.We can drop the database using drop database as well as dropdb command. We cannot rollback the statement of drop database .
Recommended Articles
This is a guide to PostgreSQL DROP DATABASE. Here we discuss an introduction to PostgreSQL DROP DATABASE, how does it works and examples. You can also go through our other related articles to learn more –
