Drop all tables in postgresql?

You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE. From a bash script:

#!/bin/bash
TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

echo Dropping tables:${TABLES}
psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"

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;

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.

drop schema public cascade;

should do the trick.

Just in case... Simple Python script that clean Postgresql database

import psycopg2
import sys

# Drop all tables from a given database

try:
    conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
    conn.set_isolation_level(0)
except:
    print "Unable to connect to the database."

cur = conn.cursor()

try:
    cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
    rows = cur.fetchall()
    for row in rows:
        print "dropping table: ", row[1]   
        cur.execute("drop table " + row[1] + " cascade") 
    cur.close()
    conn.close()        
except:
    print "Error: ", sys.exc_info()[1]

Make sure that after copying it the indentation is right since Python relies on it.

Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:

psql -U $PGUSER $PGDB -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB

NB: either set or replace $PGUSER and $PGDB with the values you want

If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.

DROP FUNCTION IF EXISTS remove_all();

CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
    rec RECORD;
    cmd text;
BEGIN
    cmd := '';

    FOR rec IN SELECT
            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace
        WHERE
            relkind = 'S' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
            'DROP TABLE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace WHERE relkind = 'r' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
            'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                || ');' AS name
        FROM
            pg_proc
        INNER JOIN
            pg_namespace ns
        ON
            (pg_proc.pronamespace = ns.oid)
        WHERE
            ns.nspname =
            'public'
        ORDER BY
            proname
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    EXECUTE cmd;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT remove_all();

Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:

psql -f clean_all_pg.sql

Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.

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';