How to drop all the tables in a PostgreSQL database

Overview

There are two ways in which we can drop all the tables in a PostgreSQL database.

1. By dropping the schema
2. By dropping individual tables

Dropping the schema

In a database, a schema is an object container where the objects can be tables, data types, functions, or operators.

For more information on database schemas, you can look at this Answer.

Dropping a schema will remove the tables contained in that schema. However, the schema and its permission need to be re-created.

Let’s make the schema name public. The SQL statements to drop a schema are as follows:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

1. First, we drop the schema (including all the objects contained in the schema) using the DROP SCHEMA statement.
2. We re-create the public schema using the CREATE SCHEMA statement.
3. We give all the privileges on the newly created public schema to users postgres and public.

This method of deleting the tables from a database is a bit risky, which makes it unsuitable for development.

Dropping individual tables

In this method, we get all the tables in a schema and delete the tables individually using the DROP TABLE command. This method is suitable for the production environment, as other objects in the schema are retained.

PostgreSQL stores all the tables and the table metadata on its record table called pg_table.

The query is as follows:

SELECT
  'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' 
from
  pg_tables WHERE schemaname = 'public';

We select all the tables from pg_tables whose schemaname is public. Using subquery, we delete the tables from the schema.