How Can I Drop All the Tables in a Postgresql Database?

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;

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.

The most accepted answer as of this writing (January 2014) is:

drop schema public cascade;
create schema public;

This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

However, by contrast a brand new database will have the following:

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
  IS 'standard public schema';

For me using a python web framework which creates database tables (web2py), using the former caused problems:

<class 'psycopg2.ProgrammingError'> no schema has been selected to create in 

So to my mind the fully correct answer is:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

Also note to issue these commands in pgAdmin III, I used the Query tool ( magnifying glass icon "Execute abritrary SQL queries") or you could use Plugins-> PSQL Console

Note

If you have any extensions installed they will be dropped when you drop the schema, so you should make note of what you need installed and then execute statements as necessary. E.g.

CREATE EXTENSION postgis;

You can drop all tables with

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.

Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.

If everything you want to drop is owned by the same user, then you can use:

drop owned by the_user;

This will drop everything that the user owns.

That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).

You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.

More details in the manual:

As per Pablo above, to just drop from a specific schema, with respect to case:

select 'drop table "' || tablename || '" cascade;' 
from pg_tables where schemaname = 'public';

This is a really interesting question, and you'll get it done in Multiple ways:

1. By dropping and recreating the current schema

Here, In general, we have a public schema by default. So, I'm using it as an instance.

-- Recreate the schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Restore default permissions
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

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

Pros:

This will clean an entire Schema and re-create it as a new one.

Cons:

You'll lose other entities too like Functions, Views, Materialized views, etc.