Delete all tables in a PostgreSQL schema without dropping the schema itself
Did you ever have had to re-load a postgres database without having the privileges needed to create a database/schema nor the ones needed to drop it ? Well ... I had to today. The schema had 220 tables, thus dropping each table one by one was not an option. With a little bit of patience and some googling I came out with this :
psql -h <server name/ip address> -U <user> -t -d <database> -c "SELECT 'DROP TABLE ' || n.nspname || '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables
then
psql -h <server> -d <database> -f /tmp/droptables
Let's explain it a little .
The first command creates, for every relation of the schema 'public', a string of the form 'DROP TABLE public.[relation name]; and appends it to the file /tmp/droptables ; the result is a file that contains a set of SQL drop instructions that just fits your schema.
The second command is simply issuing all the SQL you just generated to the database.
Happy dropping !!!