Delete all table without dropping database in postgres in django dbshell in one command?

Question

I tried this ..

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

but doesn't seems to work out for one command?

Answer 1

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;

Drop all tables in PostgreSQL?

see above link for more answers

• Irshadmi4: ERROR: must be owner of schema public
• Irshadmi4: yes i did , may be syntax is correct but some sort authentication need to be handle .... dono what ?
• Haji: have you specified owner as anything in the schema property window?
• Jay: If you're not currently the superuser (postgres) then the last step should also be ALTER SCHEMA public OWNER TO postgres;
• RichardP: Be aware that if you do this, then all users that you created will no longer be able to access the schema, and all queries will fail for that user. You will need to give the user access to the schema: grant usage on schema public to dbuser; You must be the owner of the schema to do this, so also ensure you have followed Jay's advice in the comment above.

Answer 2

Run the following bash script:

psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db> -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db>

I copied from here: http://www.commandlinefu.com/commands/view/12989/postgresql-drop-all-tables-from-a-schema

It worked for me.