Drop Database PSQL

In this PostgreSQL tutorial, we are going to learn about Drop Database psql. Here we will learn how to use the drop queries available in PostgreSQL, and we will also cover the following list of topics.

• Drop database psql
• Drop database psql command line
• Drop database psql force
• Drop database psql terminal
• Drop database psql shell
• Drop all databases psql
• Drop multiple databases psql
• Psql drop database with connections
• Drop all tables in database psql
• Psql drop database before restore

Drop database psql

If a PostgreSQL database is no longer required, we drop it by using the DROP DATABASE statement. In PostgreSQL, DROP DATABASE is a statement that permanently removes all catalog entries and data directory.

Only the database owner can execute this command. We can’t execute while someone is connected with the target database. We need to connect to another database to execute the DROP DATABASE command.

Let’s check the syntax for the DROP DATABASE statement.

DROP DATABASE [IF EXISTS] database_name;

Here, IF EXISTS is an optional parameter. It will not throw an error if the database does not exist. In this case, a notice is being issued. The database_name mentions the DB name that we want to drop or remove.

Now, we will discuss step by step process to drop the database using the Postgres command line.

Step 1: We will use \l or \list command to list all the currently available databases. Let’s check the output by implementing the \l command.

Step 2: To drop or delete any database in Postgresql, we will enter the command given below.

DROP DATABASE <database_name>;

Now, we will take any database from the following above databases given in the output. Let’s take the database cars. So, we will write the following command to delete it.

DROP DATABASE cars;

Let’s check the output for it.

Step 3:  We will again try to delete the same database i.e. cars but we will get an error. Let’s check after implementing it.

Step 4: Now, we will delete the same database with IF EXISTS clause and we will get a warning. Let’s check the query for its implementation.

DROP DATABASE IF EXISTS cars;

Let’s check output for it.

Read PostgreSQL WHERE with examples

Drop database psql force

We will describe a new characteristic in PostgreSQL that will let the DROP DATABASE command be executed even if active sessions are connected to the database. DROP DATABASE command was supported from the early versions of Postgresql.

But until now, DROP DATABASE was not allowed if any of the sessions connected to the database being dropped was active.

Now has been added a feature to allow DROP DATABASE even if any active sessions are connected to the database. Let’s check the syntax for it.

DROP DATABASE IF EXISTS databasename WITH (FORCE); 

And if we want to cross-check whether the database is actually deleted or not. We will check with the help of the below query.

DROP DATABASE a1 WITH (FORCE); 

DROP DATABASE IF EXISTS a1 WITH (FORCE);

Let’s check the implementation of the above query.

Read PostgreSQL CASE with Examples

Drop multiple databases psql

Postgre doesn’t provide any selected command or function to drop multiple databases. Like there is no direct way for dropping multiple databases at once.

To drop multiple databases, we will build an unknown code block and then will execute that particular code block using the psql tool.

Now, we will generate drop database commands by using the below query which we were talking about.

select 'drop database "'||datname||'";'
from pg_database
where datistemplate=false;

Let’s check the output for the above query.

Read PostgreSQL WHERE IN with examples

Drop database psql shell

Now we will learn how to drop the database using the psql tool in the shell. We can drop the database in the shell using the Linux terminal or Ubuntu. So, we will learn how Postgresql queries are implemented in Ubuntu.

Firstly we will check for the list of databases using \l or \list command. Now we will create a database by the name cars. Let’s see in the below output.

Now we will drop the database cars using the query dropdb cars. dropdb will destroy an existing PostgreSQL database. The user who will execute this command should be a database superuser or the owner of the database.

Let’s check whether the database cars is deleted or not by using the command \l. Below is the output for it.

Read PostgreSQL Update

Drop all databases psql

Now we will learn how we can drop all databases in Postgresql using the psql tool. For deleting all databases in Postgresql we use the DROP DATABASE statement doesn’t just have to drop one database.

Here we will learn that all databases can be specified when separated by commas. Let’s see it in the below output.

Now we will drop all databases which will give the below output.

Read Postgresql date comparison

Psql drop database with connections

Now we will learn how we can drop databases with active connections using the psql tool. While using PostgreSQL some time we may come forward to a case to drop the whole database.

If that database is actively getting used we may get an error that can’t drop the database due to active connections.

Here are the two steps through which we can drop all active connections. Let’s learn how it is implemented.

• Step 1 : Firstly we will connect the server using admin login which will switch to default database Postgres. Now, we will run the script to drop all the active connections. Below is the script which will drop all the active connections.

SELECT  pg_terminate_backend (pg_stat_activity.pid) 
FROM  pg_stat_activity WHERE  pg_stat_activity.datname ='DatabaseName';

• Step 2 : Next step is that we will simply drop the database by the query given below.

DROP DATABASE "DatabaseName";

Now, we will implement the above two queries.

Read Postgresql date to string

Drop all tables in database psql

PostgreSQL doesn’t provide any selected command or function to drop all tables from a database. To drop all tables, we will build an unknown code block and then will execute that particular code block.

For performing this function, we tend to choose all table names for a schema from the pg_tables and will store the names within the RECORD type variable.

After that point loop through these table names and will execute the drop table cmd for every table name.

We have a detailed discussion on dropping all tables in the Postgresql database using the psql tool. Click here for the link

Read Postgresql escape single quote

Psql drop database before restore

To export the PostgreSQL database we will have to use the pg_dump tool, which will dump all the contents of a particular database into one file.

We need to run pg_dump within the command line on the pc wherever the database is stored.

If the database is stored on a remote server, we will need to SSH to that server in order to run the below command.

pg_dump -U db_user -W -F t db_name > /path/to/your/file/dump_name.tar

Here we have used the below options :

• -U is used to specify that the user can connect with the PostgreSQL database server.
• -W or — is used for passwords that may force pg_dump to prompt for a password before connecting to the server.
• -F is used to specify the format of the output file, which may be one among the following:
  • p – plain-text SQL script
  • c – custom-format archiv
  • d – directory-format archive
  • t – tar-format archive

To check a list of all the available options use pg_dump.

With the choices given in the above query, pg_dump can first prompt for a password for the database user db_user and so connect as that user to the database named db_name.

Once it is connected, it will write the output generated by pg_dump to a file with a given name, during this case, dump_name.tar.

The file created during this method contains all the PostgreSQL queries that are needed so as to replicate our database.

There are two methods to restore a PostgreSQL database. One is psql which is used for restoring from a plain PostgreSQL script file created with pg_dump.

Another one is pg_restore is used for restoring from a .tar file, directory, or custom format created with pg_dump. Now, we will further discuss

• Firstly, we will learn how to restore a database with psql

In case our backup may be a plain-text file containing SQL script, later we will restore our database by using PostgreSQL interactive terminal. Now we are going to run the below command.

psql -U db_user db_name < dump_name.sql

In the above command where db_user is that the database user, db_name is that the database name and dump_name.sql is that the name of our backup file.

• Second step is to restore a database with pg_restore

If we select custom, directory, or archive format when creating a backup file, then we need to use pg_restore in order to restore our database.

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

If we are using pg_restore then we have various options available like

• -c which will drop database objects before recreating them,
• -C which will create a database before restoring into it, -F which will format to specify the format of the archive,
• -e which will exit if an error has been encountered.

You may also like the following PostgreSQL tutorials:

• PostgreSQL list users
• Postgresql function return table
• Postgresql difference between two timestamps
• PostgreSQL Date Difference Examples
• Create a stored procedure in PostgreSQL

In this PostgreSQL tutorial, we have learned about Drop Database psql. Here we have learned how to use the drop queries available in PostgreSQL, and we have also covered the following list of topics.

• Drop database psql
• Drop database psql command line
• Drop database psql force
• Drop database psql terminal
• Drop database psql shell
• Drop all databases psql
• Drop multiple databases psql
• Psql drop database with connections
• Drop all tables in database psql
• Psql drop database before restore