Today, we will discuss a very dangerous question. How to drop all tables in a PostgreSQL database?

This is a very dangerous question, as i see, thats very popular question, i would like to bring it here.

The Wrong Way

On internet you can find statements like:

DROP SCHEMA schema_name;
CREATE SCHEMA schema_name;

And grant permissions again.

I call this method as the wrong way. Because some schemas in PostgreSQL is important. Predefined schema in PostgreSQL is public schema to create tables. Dropping a schema is dangerous, because you may miss some table, drop a necessary object or vice versa.

Tip of The Day

Don’t use Public schema.

Schemas, basically, a logical definition to group objects / tables created for same purpose. Public schema is a predefined schema in PostgreSQL, if you define objects under it, you won’t be able to group tables.

For any purpose, in the beginning of a project, create a schema.

Correct Way to Drop All Tables in a PostgreSQL Database

When a DBA drops objects in a database, DBA should preview the script first, so we need a script in first place. Hopefully, we can use SQL to create that script:

SELECT 'DROP TABLE ' || table_schema || '.' || table_name || ';' FROM information_schema.tables WHERE table_schema = 'my_schema';

Statement above will generate a script for you to drop all tables in a schema. If you remove WHERE clause in statement you will get a script to drop all tables in a database.

You can run script to drop all tables in a PostgreSQL database.

Please leave a comment below if you have a question , or anything to add. I will try my best to get bcack to you as soon as possible.