How to Drop All Tables in PostgreSQL

In this comprehensive guide, I’ll walk you through various methods for dropping all tables in PostgreSQL, ranging from simple commands to more advanced techniques. By the end of this tutorial, you’ll have the complete knowledge to clean up your database.

How to Drop All Tables in PostgreSQL

Before diving into the methods, let’s consider some common scenarios where dropping all tables becomes necessary:

• Setting up a clean testing environment
• Resetting a development database
• Migrating to a new database schema
• Troubleshooting database issues
• Implementing a complete database redesign

Now, let’s examine the various methods for dropping all tables in PostgreSQL.

Approach 1: Using DROP SCHEMA

The command ‘DROP SCHEMA CASCADE’ drops or removes all the tables and related objects that exist in the schema. It is irreversible and permanently deletes all the tables.

The syntax is given below.

DROP SCHEMA schema_name CASECADE;

Where schema_name is the name of the schema that you want to drop, and this schema contains all the tables.

For instance, we will delete the schema ‘public’ that exists in the PostgreSQL database ‘postgres’. You can choose any schema that exists in any database.

First, open psql and log in to the database ‘postgres’. If the below command asks for a password, provide the password for the user.

psql -U postgres -d postgres

Run the command below to view all schemas within the database ‘postgres’.

\dn

After running the above command, we found the schema ‘public’ as shown in the screenshot below.

Now drop the schema ‘public’ that contains all the tables of the database ‘postgres’.

DROP SCHEMA public CASCADE;

View the existence of the schema ‘public’ using the command below.

\dn

After executing the above query, I got the expected output, and all the tables got deleted successfully, as shown in the screenshot below.

In the above output, you can see that the schema ‘public‘ has been deleted, which contains many tables shown in the red rectangle.

Approach 2: Using pgAdmin

You can use the GUI (Graphical User Interface) called pgAdmin of PostgreSQL to drop all the tables that lie within the specific schemas. Follow the steps below to drop all the tables in PostgreSQL.

To drop all tables in PostgreSQL, follow the steps below.

1. First, open the pgAdmin application and connect to the PostgreSQL server, as shown in the picture below. If the server asks for a password, enter the password you defined when creating the PostgreSQL server.

2. Expand the Browser section and select the database where the schema exists. Then select the schema that contains all the tables, as shown in the picture below.

3. Right-click on the selected schema ‘public’ and choose the option ‘Delete/Drop’.

4. A dialog appears asking about dropping the schema; then, click the Yes button and proceed to delete the ‘public’ schema, which contains all the tables.

If you again check the ‘Schemas’ section of the database ‘postgres’, you won’t find the schema ‘public’ because it has been dropped.

Approach 3: Using DROP TABLE

The ‘DROP TABLE’ command can also be used to drop all tables from the database, but in this case, you must drop tables one by one, which is a time-consuming process.

The syntax is given below.

DROP TABLE table_name;

• DROP TABLE: The command to drop a particular table from the database.
• table_name: Name of the table that you want to drop.

Suppose you have a database called ‘company_info’ that stores information about all departments, employees, and other relevant details. This database contains multiple tables, and you want to drop all the tables.

For example, the ‘company_info’ table has the following subtables, which are illustrated in the picture below.

Let’s start with a table named ‘customers’ using the below command.

DROP TABLE customers;

After running the above command, the ‘customers’ table is dropped from the database. Let’s check the existence of this table using the command below.

\d

When you re-view the list of tables, the above picture doesn’t contain the ‘customers’ table. You can repeat the above steps for each table in the database to drop all the tables.

Approach 4: Using the PSQL Command Line Tool

If you prefer working with the command line, the psql utility provides powerful options for dropping all tables using the command below.

psql -U username -d database_name -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

Best Practices

Before dropping all tables in a PostgreSQL database, consider these necessary precautions:

1. Always Back Up Your Data First

pg_dump -U username -d database_name -f backup.sql

This creates a complete backup of your database before making any destructive changes.

2. Verify the Target Database

Double-check which database you’re connected to before executing DROP commands. A simple query can help:

SELECT current_database();

3. Consider Dependencies

When dropping tables with relationships, use the CASCADE option to avoid dependency errors. However, be aware that CASCADE will remove all dependent objects.

4. Test in a Non-Production Environment

Always test your table-dropping scripts in a development or staging environment before applying them to production.

5. Check for Permissions

Ensure you have the necessary permissions to drop tables in the database:

SELECT has_schema_privilege(current_user, 'public', 'CREATE');

Conclusion

Dropping all tables in PostgreSQL doesn’t have to be a very critical task. With the approaches outlined in this article, you can select the approach that best suits your specific needs.

Remember that dropping all tables is a destructive operation, so always back up your data before proceeding. By following the best practices and precautions discussed, you can safely reset your PostgreSQL database and continue your development.

You may like to read the following articles:

• How to Create View in PostgreSQL
• Postgres Export to CSV | Export PostgreSQL Table To CSV File
• PostgreSQL change column data type
• PostgreSQL Date Add