Postgres provides a couple of ways to drop all the tables of a Postgres database, such as dropping the complete schema or removing the schema’s tables individually. The DROP SCHEMA command is used to drop the entire schema, while the DROP TABLE command with a for loop can drop each table individually.

This post will show you how to drop/delete tables in Postgres using suitable examples.

Method 1: Drop the Complete Schema

Dropping a complete schema will drop all the objects present within it. However, the selected schema, along with its privileges, must be re-defined/re-created. The schema may have some tables with dependent objects; therefore, the CASCADE option with the DROP SCHEMA statement must be used to remove the dependent objects:

DROP SCHEMA schema_name CASCADE;
CREATE SCHEMA schema_name; 
GRANT ALL ON SCHEMA schema_name TO postgres;
GRANT ALL ON SCHEMA schema_name TO public;

The command mentioned above will drop all the tables of the selected schema along with its dependent objects like views and foreign keys. After that, it will re-create a schema and assign all privileges to the “postgres” and “public” users.

Listing Available Schemas

Execute the “\dn” command from the SQL Shell to list all the schemas:

The above snippet shows that there are seven schemas available in the “postgres” database.

Example: How Do I Drop All the Tables From a Single Schema?

The “example_schema” contains various tables, as shown in the following snippet:

\dt example_schema.*;

Suppose we need to drop all the tables from the “example_schema”. For this purpose, we must use the “DROP SCHEMA” statement followed by the “schema_name” and then the “CASCADE” option:

DROP SCHEMA example_schema CASCADE;
CREATE SCHEMA example_schema;

The output shows that the first, the “example_schema,” has been dropped along with its objects. After that, the CREATE command re-created the selected schema. Now, grant the privileges to the “postgres” and “public” users via the following commands:

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

Let’s verify whether all the tables of the “example_schema” have been dropped or not via the following command:

\dt example_schema.*;

The output signifies that all the tables of the selected schema have been dropped.

Note: Similarly, you can drop all the tables from multiple schemas using the comma-separated syntax. To do that, you must use the DROP SCHEMA command as follows:

DROP SCHEMA schema_1, schema_2, schema_3 CASCADE;
CREATE SCHEMA schema_1, schema_2, schema_3;
GRANT ALL ON SCHEMA schema_1, schema_2, schema_3 TO postgres;
GRANT ALL ON SCHEMA schema_1, schema_2, schema_3 TO public;

Replace "schema_1, schema_2, schema_3" with the name of the schemas that you want to drop.

Method 2: Drop Each Table of a Schema Individually

Alternatively, we can use a script to generate the DROP TABLE commands for each table in the database and execute them individually. This process is efficient and highly recommended because it drops only the tables of a schema rather than the whole schema.

Example: How Do I Drop All the Tables Individually?

Let’s execute the below command to see all the available tables from the “public” schema:

\dt;

Suppose we want to remove/drop all the relations of the “public” schema. For this purpose, the following script will be used:

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

We utilized the for loop in the above code block to iterate over all the tables of the “public” schema. The DROP TABLE statement is used along with the “IF EXISTS” and “CASCADE” options to first check the table’s existence and then drop all the tables along with their dependent objects:

The “DO” message in the output demonstrates that the above block of code executed successfully. Let’s verify whether all the tables of the “public” schema have been dropped or not via the following command:

\dt;

The output proves that all the tables from the “public” schema have dropped successfully.

This way, you can drop all the tables in Postgres.

Conclusion

Postgres provides a couple of ways to drop all the tables of a Postgres database, such as dropping the complete schema or removing the schema’s tables individually. Dropping a complete schema will drop all the objects present within it. Alternatively, we can use a DROP TABLE command with the for loop to drop all the tables of the selected schema. This post demonstrates several methods to drop/remove all the tables in Postgres.