How to Drop Multiple Databases in PostgreSQL
Tags: postgres, database, sql
Published: 2021-10-28 16:42:20

---
title: "How to Drop Multiple Databases in PostgreSQL"
published: true
description: "Various ways to drop multiple databases in PostgreSQL"
tags: ["PostgreSQL", "postgres", "database", "sql"]
canonical_url: https://kags.me.ke/post/how-to-drop-multiple-databases-in-postgresql/
---

![alt="drop multiple databases in postgresql"](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e1ja0mov84ai1v6qjal8.png)

## Introduction
While performing database integration tests, things went south, and I ended up with more than 15 (fifteen) temporary PostgreSQL test databases.

I use [OmniDB](https://github.com/OmniDB/OmniDB), an open-source application for managing databases. Version (3.0.2b) of OmniDB does not have an option for selecting and dropping multiple databases. To drop a database in OmniDB, you right-click on a database, select **Drop Database** from the context menu, run the query to drop the database, and finally close the query window. The thought of repeating the four steps on all those test databases led me to think of other efficient alternatives.

The following three alternatives for dropping multiple databases in PostgreSQL came to mind:
  1. Anonymous Code Block
  1. Interactive Terminal
  1. Using a shell script

My test databases are prefixed with **test**; I will extract the names of the databases from [`pg_database`](https://www.postgresql.org/docs/current/catalog-pg-database.html) database using `SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false` SQL query. The WHERE clause in the SQL query will restrict the database names to my test databases and non-template databases.

In the following sections, I explore the above three options in dropping multiple databases in PostgreSQL.

## Anonymous Code Block
The [**DO**](https://www.postgresql.org/docs/current/sql-do.html) statement in PostgreSQL executes an anonymous code block. In the past, I have used an anonymous code block to [drop tables from a database](https://kags.me.ke/post/postgresql-how-to-drop-all-tables/).

```sql
DO $$
  DECLARE
      r RECORD;
BEGIN
    FOR r IN
        SELECT datname
        FROM pg_database
        WHERE datname LIKE 'test%' AND datistemplate=false
    LOOP
        EXECUTE 'DROP DATABASE ' || quote_ident(r.datname) || ';' ;
    END LOOP;
END $$;
```

This time, however, the anonymous code block throws an exception:

```bash
DROP DATABASE cannot be executed from a function
CONTEXT: SQL statement "DROP DATABASE test_01024da6a3;"
PL/pgSQL function inline_code_block line 12 at EXECUTE
```
!["Drop multiple databases in anonymous code block"](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vgq2ic48uvb4oum1j9dl.png)

As it turns out, you  [cannot execute DROP DATABASE  statements inside a transaction block](https://www.postgresql.org/docs/current/sql-dropdatabase.html) and [anonymous code blocks being functions are transactional](https://www.postgresql.org/docs/current/plpgsql-transactions.html) and this is the reason for the exception.

## Interactive Terminal

PostgreSQL interactive terminal (**[psql](https://www.postgresql.org/docs/current/app-psql.html)**) allows you to enter, edit, execute commands, and view results of SQL queries. In addition to SQL queries, **psql** provides meta-commands which are commands processed by **psql** without being sent to a PostgreSQL server; all meta-commands have a backslash (\) prefix. In the following paragraphs, I explain how to dynamically create a `DROP DATABASE` SQL query and run the query using the `\gexec` meta-command.

Before you execute PostgreSQL SQL commands, you have to connect to a PostgreSQL server. To connect to a PostgreSQL server,  run the `psql -d postgres` command from a terminal window. If you don't specify the user name,  PostgreSQL will default to the current operating system user. The default user has permission to create and drop databases. You can connect as a different user by appending the **-U** command-line option to the `psql` command (`psql -U username -d postgres`).

Upon successful connection, the `psql` prompt you get will either be `postgres=>` or `postgres=#` if you are a database superuser. At the prompt,  type your SQL query to construct `DROP DATABASE` statements for the databases you want to drop. Do not terminate the query with a semicolon (;) --`SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false`. If you terminate the query with a semicolon (;), the query will run when you press the **RETURN** key.

The `\gexec` meta-command sends the current query buffer to the server and treats each column of each row of the query's output as an SQL statement to be executed. In our case, our query will output a **DROP DATABASE** SQL query for each test database. `\gexec` meta-command will then run each statement from the output.

```bash
postgres=#
postgres=# SELECT 'DROP DATABASE ' || quote_ident(datname) || ';'
postgres=# FROM pg_database
postgres=# WHERE datname LIKE 'test%' AND datistemplate=false
postgres=#
postgres=# \gexec
```


## Using Shell Script
Scripts help in automating repetitive tasks. You create a text file, type commands in the file, make the file executable, and any time you want to repeat the commands, you execute the script file instead of retyping the commands.

Creating and executing a shell script involves the following steps:
1. Use a text editor to create your script file
2. Insert commands in the file.
3. Save and close the file.
4. Make the script executable.
5. Execute the script.

Create a _drop-test-dbs.sh_ text file and update with the following:


```bash
#!/bin/bash

test_databases_file=~/projects/test_dbs.txt
psql -d postgres -c "COPY (SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false) TO '$test_databases_file'"

while read dbname
do
  echo "dropping DB $dbname..."
  dropdb "$dbname"
done < $test_databases_file

echo "removing $test_databases_file file"
rm $test_databases_file
```


`#!/bin/bash` is called a **shebang** or a **bang** line. It specifies the interpreter that will execute the commands.

The script uses **psql** to connect to a PostgreSQL server, run SQL query to fetch names of the test databases and [copy](https://www.postgresql.org/docs/current/sql-copy.html) the database names to a _test_dbs.txt_ file. We then open the created file (containing database names), and for each database name, we drop the database using the [dropdb](https://www.postgresql.org/docs/current/app-dropdb.html) utility program. Finally, we remove the file containing the database names.

After creating our script file, we then open the console or terminal window and assign the execute permission to the file using the **chmod** command (`chmod +x drop-test-dbs.sh`).

Finally, to drop the databases, we execute the commands in the file by running the file from a console or terminal window (`./drop-test-dbs.sh`). If the script file is not within the current folder, remember to include the file path (`file-location/drop-test-dbs.sh`).

## Wrapping Up

There are various methods for dropping multiple databases in PostgreSQL.

ou can use desktop or web-based database management tools to drop databases. Some database management tools allow dropping one database at a time; others can drop multiple databases.

You cannot drop PostgreSQL databases within a transaction; PostgreSQL functions (including anonymous functions) are transactional.

`psql` is a powerful command-line tool that ships with PostgreSQL. You can use `psql` to connect to a server and interact with databases. Using `psql` and querying the `pg_database` database for the required databases together with `\gexec` meta-command is another option for dropping multiple databases in PostgreSQL. However, failing to filter for required databases will drop all databases from your server.

If the task of dropping multiple databases is repetitive, you can consolidate the `psql` commands into a shell script file and execute the script file.

If you would like to create multiple databases for testing the methods described in this post, you can create and run a script file with the following commands:
     

```bash
#!/bin/bash

for n in {1..15}
do
  # Use Bash command substitution to store the
  # output (GUID) from uuidgen command
  DB_ID=$(uuidgen)

  # Use Bash Substring expansion to extract
  # 8 characters of the GUID starting from index 0
  db="test_db_${DB_ID:0:8}"

  createdb "$db"
  echo "DB $db created"
done
```