Delete All Tables in a Postgres database

This doesn’t happen every day, but once in a while you may find yourself needing to remove all the tables/functions/views from a PostgreSQL database. The key is you don’t want to delete the database itself. Maybe you are on a hosted solution where you don’t really have access to drop/restore a database. For example, I’m using Heroku’s Postgres service and dropping the database is not an option.

Here is a simple command that will remove all the tables and restore to new.

psql postgres://USER:PASSWORD@HOSTNAME/DATABASE -c " DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT USAGE ON SCHEMA public to PUBLIC; GRANT CREATE ON SCHEMA public to PUBLIC; COMMENT ON SCHEMA public IS 'standard public schema';"

Assumptions

You have the right to do this. You can check in psq with \dn+

my_database=> \dn+
                                   List of schemas
  Name  |     Owner      |        Access privileges         |      Description
--------+----------------+----------------------------------+------------------------
 public | MY_USER        | MY_USER=UC/MY_USER+              | standard public schema
        |                | =UC/MY_USER                      |
(1 row)

The schema you are trying to drop is the public schema.
You can access your database to run commands.

Bonus: How to read the “Access privileges”

You may be wondering what that cryptic permissions list above even means. Like me, if you find yourself confused, there are a few key things to keep in mind. Maybe you come from a Unix background and the + seems like it’s adding a permission?

The + are part of the way psql formats the result, they are not part of the value. (ignore it)

To me that was really confusing. I also didn’t realize that each new line represented a new access privilege. This is confusing because =UC/MY_USER doesn’t resemble the above one. It’s the access for PUBLIC. We get from the above SQL commands GRANT USAGE ON SCHEMA public to PUBLIC; GRANT CREATE ON SCHEMA public to PUBLIC;

Straight from the PG docs

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

Credits: