PostgreSQL Delete/Drop DatabaseThe Drop/delete command is used to eternally delete all the file entries and data directory from the PostgreSQL platform. Therefore, we have to use this command very carefully. In this section, we are going to learn how to drop or delete the database, which we don't need any more in PostgreSQL. In PostgreSQL, we can drop the database in two ways: - Drop Database PgAdmin
- Drop Database Using SQL Shell
Drop Database PgAdmin (Graphical User interface)To create a database in pgAdmin, we are going to follow these below steps: Step 1 - Firstly, we will open the pgAdmin in our local system.
- Select the database (Javatpoint) by a left-click on it.
- Then right-click on the Javatpoint
- After that, click on Delete/dropoption from the given drop-down list to delete the database.
Step 2 - Once we click on the Delete/Drop option, one confirmation pop-up will appear on the screen, where we click on the Yes button to drop the database.
See the result - As soon as we click on the Yes button, the database is deleted immediately from the record.
Drop Database Using SQL Shell (Command Line)In this, we are going to drop the database in the SQL Shell (Command Line). Syntax The syntax for Dropping the database is as follows: Syntax: Where the command contains the following parameters: Parameters | Description |
---|
IF EXISTS | It is an optional parameter; where the warning is displayed in the place of an error if the database does not exist. | name | Here, we will reference the database name that we want to drop it. |
Now, we are going to use this command in the command line: Step 1 - Open the SQL shell and type the below command to see the existing database.
Step 2 - For dropping the database, we will enter the below command:
- While using this above command, we may encounter this below error:
To delete the Javatpoint database, we need to follow the below process: - First, we have to Revoke the connection with the help of below command:
- And then press the Enter key.
- Once the connection is Revoked, we will enter the following query:
- Then enter the drop database query and use the \l command to verify whether the database is deleted or not as we can see in the below screenshot:
Step 3 - If we try to drop the same database again, we will get the following error as we can see in the below screenshot:
Step 4 - Then we are going to drop the database with IF exist condition, and we get the below warning:
Using dropdb commandIn PostgreSQL, the dropdb is a command-line executable command which covers the SQL drop database command. This command can only be run by those end-users who is the owner of the database or a database superuser. We can remove the database remotely with the help of the dropdb statement. Syntax The syntax for dropdb is as following: where the option could be the following: Options | Description |
---|
-e | Here e means Echo, which is used to create and send to the server. | -i | It is used to show the verification prompt before operating any fatal job. | --help | It allows us to help with dropdb command-line statements. | -h host | It defines the name of the host of the system, where the server is directly executing. | -p port | This option defines the Unix domain socket file extension, where the server is creating the connections. | -V | we can use -V option, to print the dropdb version. | -U username | It is used to display the user name. | -w | if we don't need a password screen, we can use this option. | maintenance db-=dbname | To connect the database for dropping the target database, we will use this option to describe the database name. | --if exists | This option will display an error rather than a warning if the database does not exist. | -W | This option is used to prompt for a password before dropping the database. |
Let us see an example where we are deleting a database from Operating system command prompt: Here, we will use the Postgres username to drop the database. And the above command drops the javatpoint database. Overview- The drop database command is used to delete all the file entries and data directory permanently from the PostgreSQL platform.
- With the help of the pgadmin tool, we can also drop the database
- We can remotely drop a database by using the dropdb command.
|