Fermasoft.com

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.

The funny thing is that once I came up with it I even script-ed it :

 
#!/bin/bash
die () {
    echo >&2 "$@"
    exit 1
}

if [ $# -eq 0 ] ; then
 die "usage : droptables server user database"
elif [ $# -eq 1 ] ; then
 die "usage : droptables server user database"
elif [ $# -eq 2 ] ; then
 die "usage : droptables server user database"
elif [ $# -eq 3 ] ; then
    SERVER=$1
    USER=$2
    DATABASE=$3
else
 die "usage : droptables server user database"
fi

echo "s:$SERVER u:$USER d:$DATABASE"


echo "collecting informations to drop data in /tmp/droptables; please provide a password if required : "
psql -h $SERVER -U $USER -d $DATABASE -t -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
echo "dropping all tables ; please provide a password if required : "
psql -h $SERVER -d $DATABASE -U $USER -f /tmp/droptables

Happy dropping !!!