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;