How to Drop All Tables in PostgreSQL

In this PostgreSQL tutorial, I will show you how to drop all tables in PostgreSQL.

Drop All Tables in PostgreSQL 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 deletes all the tables permanently.

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 database ‘postgres’ of PostgreSQL. You can choose any schema that exists in any database.

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

psql -U postgres -d postgres

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

\dn

After running the above command, we found the schema ‘public’.

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 below command.

\dn

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

Drop All Tables in PostgreSQL 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 below steps to drop all the tables in PostgreSQL.

First, open the pgAdmin application and connect to the PostgreSQL server as shown in the below picture, if the server asks for a password then enter the password that you have defined while creating the PostgreSQL server.

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

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

A dialog appears asking about drop schema, then click on the Yes button and proceed to delete the schema ‘public’ that contains all the tables.

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

Drop All Tables in PostgreSQL Using DROP TABLE

The ‘DROP TABLE’ can also be used to drop all the tables from the database but in this case, you have to drop tables one by one and that 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 ‘comapny_info’ that store information about all the department, employees and etc. This database contains multiple tables and you want to drop all the databases.

For example, the ‘company_info’ has the following tables that are shown in the below picture.

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

DROP TABLE customers;

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

\d

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

Conclusion

In this PostgreSQL tutorial, we have learned how to drop all the tables from the database using approaches like DROP SCHEMA and DROP TABLE commands,s and using the GUI of PostgreSQL called pgAdmin.

You may like to read the following articles:

• How to Create View in PostgreSQL
• Postgres Export to CSV | Export PostgreSQL Table To CSV File