How to Drop All Tables in PostgreSQL

1. Drop All Tables in PostgreSQL Database
2. Drop All the Tables in a Single Schema in PostgreSQL
3. Drop All Tables by Specifying Multiple Tables in PostgreSQL
4. Drop All Tables Using an SQL Query With pg_tables in PostgreSQL
5. Drop All Tables Using table_catalog and table_schema in PostgreSQL

When developing applications, the developer must understand the different ways that they can use to interact with the database. This knowledge will help make the development more efficient because they know the best methods to approach a particular problem.

Drop All Tables in PostgreSQL Database

In this tutorial, we will learn the different ways that we can leverage to delete tables that are in a particular database without deleting the database itself.

We will start by creating a database in the PostgreSQL database management system, adding several tables, and deleting the tables we have created using different approaches.

Log in to PostgreSQL using the following command.

>psql -U postgres

Create a database that we will use to hold our database tables using the following query.

postgres=# create database customer_db;
CREATE DATABASE

View the database we have created using the \l command, which will return a collection of all the databases.

postgres=# \l

Output:

                                          List of databases
    Name     |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-------------+----------+----------+--------------------+--------------------+-----------------------
 customer_db | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 |
 postgres    | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 |
 template0   | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 | =c/postgres          +
             |          |          |                    |                    | postgres=CTc/postgres
 template1   | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 | =c/postgres          +
             |          |          |                    |                    | postgres=CTc/postgres
(4 rows)

Notice the database we have created named customer_db is displayed at the first row of the above table, and we can connect to it using the \c command.

postgres=# \c customer_db;

Output:

You are now connected to database "customer_db" as user "postgres".

We need to have some database tables in our database before we can delete them; we can use the following queries to create three tables in the database customer_db.

customer_db=# CREATE TABLE customer(id SERIAL NOT NULL UNIQUE,firstName varchar(50),lastName varchar(50),email varchar(50),PRIMARY KEY(id));

customer_db=# CREATE TABLE product(id SERIAL NOT NULL UNIQUE,productName varchar(50),productDescription varchar(50),productPrice integer,PRIMARY KEY(id));

customer_db=# CREATE TABLE cart(id SERIAL NOT NULL UNIQUE,product varchar(50),productPrice integer,productQuantity integer,totalCost integer, PRIMARY KEY(id));

The above queries create three tables, and we can use the \dt to verify that the tables have been created.

customer_db=# \dt

Output:

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | cart     | table | postgres
 public | customer | table | postgres
 public | product  | table | postgres
(3 rows)

Drop All the Tables in a Single Schema in PostgreSQL

Notice that the \dt command we have executed above returns a list of tables in our database, and the schema for this table is listed as public.

We will delete all the tables in this schema, recreate the schema, and restore the defaults authorities of the schema, as shown below.

customer_db=# drop SCHEMA public CASCADE;

Output:

NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table customer
drop cascades to table product
drop cascades to table cart
DROP SCHEMA

Dropping the schema cascades the drop operation to the three tables in our database. Create the schema again using the following command and grant all roles to the postgres and public schemas, as shown below.

customer_db=# CREATE SCHEMA public;
customer_db=# GRANT ALL ON SCHEMA public TO postgres;
customer_db=# GRANT ALL ON SCHEMA public TO public;

Drop All Tables by Specifying Multiple Tables in PostgreSQL

As shown below, we can use the SQL drop statement to delete the table by listing all the tables we want to be deleted using a comma-delimited list.

After this query, the \dt table does not find any relations proving that all our tables in the database were deleted successfully.

customer_db=# drop table if exists customer, product, cart;

Drop All Tables Using an SQL Query With pg_tables in PostgreSQL

Since our tables were dropped in the above example, recreate the three tables again using the queries provided to delete all the tables below.

We can write an SQL query that selects all the tables we want to delete and perform a cascade operation on the tables.

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

Output:

                 ?column?
------------------------------------------
 DROP TABLE IF EXISTS "customer" CASCADE;
 DROP TABLE IF EXISTS "product" CASCADE;
 DROP TABLE IF EXISTS "cart" CASCADE;
(3 rows)

Drop All Tables Using table_catalog and table_schema in PostgreSQL

We can write an SQL query as we did in the above example and provide the catalog that is our database and the public schema to delete the tables.

customer_db=# select 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;' from information_schema.tables where table_catalog = 'customer_db' and table_schema = 'public';

Output:

               ?column?
-----------------------------------------
 DROP TABLE "public"."customer" CASCADE;
 DROP TABLE "public"."product" CASCADE;
 DROP TABLE "public"."cart" CASCADE;
(3 rows)