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

    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

Answer 2

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 3

Documentation says that ./manage.py sqlclear Prints the DROP TABLE SQL statements for the given app name(s).

Answer 4

I use this script to clear the tables, I put it in a script called phoenixdb.sh because it burns the DB down and a new one rises from the ashes. I use this to prevent lots of migrations in the early dev portion of the project.

set -e
python manage.py dbshell <<EOF
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
EOF

python manage.py migrate

This wipes the tables from the db without deleting the db. Your Django user will need to own the schema though which you can setup with:

alter schema public owner to django-db-user-name; 

And you might want to change the owner of the db as well

alter database django-db-name owner to django-db-user-name;

Answer 5

\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).