How to Drop All Tables from PostgreSQL

In PostgreSQL, managing our database often includes tasks like creating, modifying, and sometimes removing tables. If we want to clear our database and remove all tables, we can do this efficiently with a few PostgreSQL commands. To clear our database without deleting the entire schema, we can drop all tables quickly and efficiently.

In this guide, we will explore how to create a table and cover two effective methods for dropping all tables from a PostgreSQL database. By following these steps, we can reset our PostgreSQL database safely and efficiently without affecting the overall database structure.

What is Dropping Tables in PostgreSQL?

In PostgreSQL, dropping a table means removing the table and its data from the database. This operation is irreversible, so once a table is dropped, the table structure and all the data it holds are deleted. However, the overall database and other database objects remain unaffected.

Why Dropping Tables is Important?

• Database Maintenance: Dropping old or unused tables keeps our database clean and organized.
  
• Development and Testing: If you're resetting a database between tests or during development, dropping all tables is faster than recreating the database.
  
• Data Reset: Dropping tables is a reliable way to reset the data without needing to drop and recreate the database itself.

How to Drop All Tables from PostgreSQL

In PostgreSQL, if we want to clear the database but keep it whole, we can drop all tables with either the DROP TABLE command for individual tables or a script that deletes all tables at once. This process is quick and efficient for resetting a database.

Step 1: Creating a Table in PostgreSQL

To make the process clear, let’s start by creating a table. Suppose we have an employees table that stores employee data, including their id, name, department, and salary. The below command will also insert five records into the employees table. We now have a basic table that contains some data for further use.

Query:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'HR', 55000),
('David', 'IT', 65000),
('Eva', 'Finance', 70000);

Output

Step 2: How to Drop All Tables in PostgreSQL

Now that we have created the employees table, let’s learn how to drop (delete) all the tables from our database. Dropping all tables can be useful when we want to reset a database or remove all objects without deleting the entire database itself.

Method 1: Using the DROP TABLE Command

The standard way to remove a table in PostgreSQL is by using the DROP TABLE command. We can remove table individually by using the below query.

DROP TABLE employees;

Output

Explanation:

This method will delete the employees table . However, if we have many tables and want to drop all of them at once, the process is slightly different.

Method 2: Dropping All Tables Using a Script

To drop all tables from our PostgreSQL database at once, we can use the following PostgreSQL query. This query will generate a list of DROP TABLE commands for every table in the current database schema. This script is helpful because it automatically finds all tables and drops them without having to list each one manually.

Query:

DO $$ 
DECLARE 
    r RECORD;
BEGIN 
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP; 
END $$;

Output

Explanation:

• The query loops through all tables in the public schema, where most user tables are stored.
  
• DROP TABLE IF EXISTS: Ensures that tables are only dropped if they exist.
  
• CASCADE: Removes all dependent objects, such as indexes, constraints, and related tables.

Step 3: Checking If Tables Are Dropped

To confirm that all tables have been successfully dropped, we can query the system catalog again. After executing the script, we can check the same by running the given below query:

Query:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Output

Explanation:

If no tables are listed, we have successfully dropped all the tables from the public schema of our database.

Example of Dropping Multiple Tables

Let’s assume we have multiple tables in your PostgreSQL database, such as employees, departments, and projects. To drop all these tables, the script mentioned earlier will delete them all at once.

1. First, create multiple tables:

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    project_name VARCHAR(50),
    department_id INT
);

2. Insert some data into these tables:

INSERT INTO departments (name) VALUES
('HR'), ('IT'), ('Finance');

INSERT INTO projects (project_name, department_id) VALUES
('Project A', 1), ('Project B', 2), ('Project C', 3);

3. Now, run the script to drop all tables:

DO $$ 
DECLARE 
    r RECORD;
BEGIN 
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP; 
END $$;

Output

Explanation:

This script will remove all user-created tables, such as employees, departments, and projects, in a single execution.

Conclusion

Dropping all tables in PostgreSQL can be accomplished easily with a script that automates the process, especially if we have many tables to remove. This method saves time and ensures that all tables and dependent objects are properly deleted. Remember to always back up our data before performing such operations, as dropping tables is a destructive action that cannot be undone.