A database can be deleted using one of two command-line options provided by PostgreSQL Drop Database with Examples: the DROP DATABASE statement or a shell program.
It’s best practice to delete inactive databases because it keeps the workspace tidy. Remember, too, that removing an existing PostgreSQL database also deletes all of the data and catalog entries for that database.
Learn how to PostgreSQL Drop Database with Examples by reading on:
- Installed and configured PostgreSQL version 10 or higher (follow our guide for Ubuntu or Windows; if already installed, check the PostgreSQL version on the system).
- The ability to access the terminal with sudo rights.
Drop Database Statement
Using the following SQL statement is the first way to delete a PostgreSQL database:
DELETE THE DATABASE NAME;
The command deletes the directory holding the catalog entries and database information. The DROP DATABASE command can only be used by the database owner. The command is not carried out if the database is actively being used by someone.
Try the following to observe how DROP DATABASE functions:
1: Start the terminal by pressing CTRL+ALT+T.
2: link PostgreSQL:
psql sudo -i -u Postgres
3: Make a database for the illustration:
Example: CREATE DATABASE;
The executed statement is printed by the terminal.
4: List each database together with:
\l
The list includes the database from the previous stage.
5: Delete the database by using the:
Example: DROP DATABASE;
The executed statement is visible in the output.
6: List each database once more:
\l
The list no longer includes the sample database.
Does Exist
For all versions where DROP DATABASE is offered, the IF EXISTS option is accessible. The following is the complete command syntax for the IF EXISTS option:
DROP DATABASE IF 'DATABASE NAME' EXISTS;
Before deleting a database, the option first verifies that it is there. The command deletes the database if one already exists. However, the tool prints a helpful alert message if a database is missing.
The procedures listed below should be followed to test the command:
1: Create the following sample database:
Example: CREATE DATABASE;
2: Drop the database by selecting “IF EXISTS”:
example: DROP DATABASE IF EXISTS;
If the database is present, the outcome is the same as using DROP DATABASE.
3: There is no longer access to the database. To see the results, rerun the command:
example: DROP DATABASE IF EXISTS;
The database is not present, according to a printed warning message.
4: Execute the following command to compare the effects of using the IF EXISTS option and not using it:
Example: DROP DATABASE;
On a non-existent database, using DROP DATABASE without the IF EXISTS option results in an error.
WITH (FORCE)
The PostgreSQL version 13 and later versions support the WITH (FORCE) option.
In-use databases cannot be deleted using the DROP DATABASE technique. The terminal prints an error stating that a database session is open if the database is already in use.
Include the WITH (FORCE) option to compel the session to end and the database to be deleted:
DELETE DATABASE (NAME OF DATABASE) WITH (FORCE);
Postgres tries to forcefully remove the database and shuts the user’s session.
Dropdb’s Utility
The DROP DATABASE command is wrapped in the dropdb shell program. In essence, the two approaches are the same. PostgreSQL Drop Database with Examples, on the other hand, provides other choices, such as remote database removal.
The fundamental syntax is:
- database name; dropdb; connection parameters; options;
Options
The dropdb utility offers the options shown in the table below.
Option | Type | Description |
-e –echo |
Option | Prints the commands that dropdb sends to the server. |
-f –force |
Option | Attempts to terminate all current connections before dropping the database. |
-i –interactive |
Option | Prompts verification before executing database deletion. |
-V –version |
Option | The console prints the utility version. |
–if-exists | Option | Prints a notice instead of an error if the database does not exist. |
-? –help |
Option | Show the help menu. |
-h <host> –host=<host> |
Connection parameter | Specifies the hostname of the machine where the server is running. |
-p <port> –port=<port> |
Connection parameter | Specifies the TCP port where the server is listening. |
-U <username> –username <username> |
Connection parameter | Connect as the specified user. |
-w –no-password |
Connection parameter | Never issue the password prompt. Useful for batch and script jobs when no user is present. |
-W –password |
Connection parameter | Force password prompt. Without the option, the server loses the connection attempt if a password is necessary. |
–maintenance-db=<database name> | Connection parameter | The option specifies the database name connection. |
Try the following command, for instance, to see how dropdb functions with the -i and -e options:
dropdb -i -e illustration
Because of the -i tag, the application requests confirmation before deleting anything.
To confirm, press Y. The application sends the server-generated commands in print form. Since there is no database, the software throws an error and terminates.
Conclusion
You are aware of two ways to PostgreSQL Drop Database with Examples after following the examples in this article. Read our article on how to delete a Postgres user to discover the various ways you can eliminate a user. Next, think about studying PostgreSQL’s various data types.