How can I drop all the tables in a PostgreSQL database?

Question

How can I drop all tables in PostgreSQL, working from the command line?

I don't want to drop the database itself, just all tables and all the data in them.

Answer 1

If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public)

DROP SCHEMA public CASCADE; CREATE SCHEMA public; 

If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.

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

Answer 2

You can write a query to generate a SQL script like this:

select 'drop table "' || tablename || '" cascade;' from pg_tables; 

Or:

select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; 

In case some tables are automatically dropped due to cascade option in a previous sentence.

Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:

select 'drop table if exists "' || tablename || '" cascade;'    from pg_tables  where schemaname = 'public'; -- or any other schema 

And then run it.

Glorious COPY+PASTE will also work.