Delete all tables in a PostgreSQL schema without dropping the schema itself




written by Marco Ferretti on January 11, 2012, at 05:56 PM

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 !!!

Comments

  1. By absolem, on April 22, 2013, at 10:38 PM Thanks a lot! On adding the user option to the second command (like shown in the bottom of your script) it worked lika a charme. : )
  2. By Marco Ferretti, on May 02, 2013, at 10:54 AM you're welcome !
  3. By ZeGuru, on January 20, 2014, at 02:04 PM Nice solution but i found an even simpler and cleaner solution here http://t.co/8hZFR36JaG
  4. By Marco Ferretti, on August 18, 2014, at 02:58 PM Hi
    thanks for the comment.
    I checked the solution but, in order for it to be usable, you have to have the permission to create schemas in the database, which is not a requirement in this scenario.
    Anyways, it looks neat !
  5. By Stephen, on May 14, 2015, at 06:20 PM Thanks! I used your script and added a PORT parameter since mine wasn't running on the default 5432.
  6. By Matteo, on November 26, 2015, at 02:51 PM Thanks for sharing!
    I've noticed that your script fails with tables named in camel-case syntax. To avoid the error the table name must be quoted, like this:

    ... SELECT 'DROP TABLE \"' || n.nspname || '\".\"' || c.relname || '\" CASCADE.....

Leave a comment

Name (required)
E-mail (required, will not be published)
Website
Comment

Enter value: Captcha