26

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?

2 Answers 2

66

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

5
  • 11
    ERROR: must be owner of schema public
    – Irshadmi4
    Commented Nov 16, 2013 at 11:24
  • yes i did , may be syntax is correct but some sort authentication need to be handle .... dono what ?
    – Irshadmi4
    Commented Nov 18, 2013 at 10:03
  • have you specified owner as anything in the schema property window?
    – Haji
    Commented Nov 18, 2013 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
    Commented Sep 18, 2017 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
    Commented Dec 28, 2018 at 20:37
11

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.

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.