23

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Irshadmi4
  • 470
  • 1
  • 3
  • 10

2 Answers2

65

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

Community
  • 1
  • 1
Haji
  • 1,999
  • 1
  • 15
  • 21
  • 11
    ERROR: must be owner of schema public – Irshadmi4 Nov 16 '13 at 11:24
  • yes i did , may be syntax is correct but some sort authentication need to be handle .... dono what ? – Irshadmi4 Nov 18 '13 at 10:03
  • have you specified owner as anything in the schema property window? – Haji Nov 18 '13 at 10:08
  • If you're not currently the superuser (`postgres`) then the last step should also be `ALTER SCHEMA public OWNER TO postgres;` – Jay Sep 18 '17 at 20:30
  • 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. – RichardP Dec 28 '18 at 20:37
10

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.

Chocksmith
  • 1,188
  • 2
  • 12
  • 40