Django PostgreSQL -- Drop all tables?

Question

Here's how I can do it when MySQL is the backend,

    cursor.execute('show tables')
    rows = cursor.fetchall()
    for row in rows:
        cursor.execute('drop table %s; ' % row[0]) 

But how can I do it when postgresql is the backend?

Answer 1

You can use select * from pg_tables; get get a list of tables, although you probably want to exclude where schemaname <> 'pg_catalog'...

Based on another one of your recent questions, if you're trying to just drop all your django stuff, but don't have permission to drop the DB, can you just DROP the SCHEMA that Django has everything in?

Also on your drop, use CASCADE.

EDIT: Can you select * from information_schema.tables; ?

EDIT: Your column should be row[2] instead of row[0] and you need to specify which schema to look at with a WHERE schemaname = 'my_django_schema_here' clause.

EDIT: Or just SELECT table_name from pg_tables where schemaname = 'my_django_schema_here'; and row[0]

Answer 2

\dt is the equivalent command in postgres to list tables. Each row will contain values for (schema, Name, Type, Owner), so you have to use the second (row[1]) value.

Anyway, you solution will break (in MySQL and PostgreSQL) when foreign-key constraints are involved, and if there aren't any, you might get troubles with the sequences. So the best way is in my opinion to simply drop the whole database and call initdb again (which is also the more efficient solution).

Answer 3

    cursor.execute("""SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type != 'VIEW' AND table_name NOT LIKE 'pg_ts_%%'""")
    rows = cursor.fetchall()
    for row in rows:
        try:
            cursor.execute('drop table %s cascade ' % row[0])
            print "dropping %s" % row[0]
        except:
            print "couldn't drop %s" % row[0]

Courtesy of http://www.siafoo.net/snippet/85