Drop all tables in PostgreSQL?

Multi tool use
Multi tool use












784















How can I delete all tables in PostgreSQL, working from the command line?



I don't want to drop the database itself, just all tables and all the data in them.










share|improve this question




















  • 2





    What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

    – Greg Smith
    Jul 25 '10 at 1:24






  • 2





    Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

    – AP257
    Jul 25 '10 at 8:34






  • 82





    DROP SCHEMA public CASCADE; -- shudder

    – wildplasser
    Jun 4 '12 at 20:18








  • 12





    @0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

    – nym
    Jun 9 '15 at 0:04






  • 2





    BTW, when you drop public, you lose any installed extensions.

    – sudo
    Jul 8 '16 at 16:53


















784















How can I delete all tables in PostgreSQL, working from the command line?



I don't want to drop the database itself, just all tables and all the data in them.










share|improve this question




















  • 2





    What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

    – Greg Smith
    Jul 25 '10 at 1:24






  • 2





    Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

    – AP257
    Jul 25 '10 at 8:34






  • 82





    DROP SCHEMA public CASCADE; -- shudder

    – wildplasser
    Jun 4 '12 at 20:18








  • 12





    @0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

    – nym
    Jun 9 '15 at 0:04






  • 2





    BTW, when you drop public, you lose any installed extensions.

    – sudo
    Jul 8 '16 at 16:53
















784












784








784


257






How can I delete all tables in PostgreSQL, working from the command line?



I don't want to drop the database itself, just all tables and all the data in them.










share|improve this question
















How can I delete all tables in PostgreSQL, working from the command line?



I don't want to drop the database itself, just all tables and all the data in them.







postgresql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Oct 12 '16 at 15:32









Oldskool

27.4k74563




27.4k74563










asked Jul 24 '10 at 23:24









AP257AP257

26.4k71168247




26.4k71168247








  • 2





    What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

    – Greg Smith
    Jul 25 '10 at 1:24






  • 2





    Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

    – AP257
    Jul 25 '10 at 8:34






  • 82





    DROP SCHEMA public CASCADE; -- shudder

    – wildplasser
    Jun 4 '12 at 20:18








  • 12





    @0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

    – nym
    Jun 9 '15 at 0:04






  • 2





    BTW, when you drop public, you lose any installed extensions.

    – sudo
    Jul 8 '16 at 16:53
















  • 2





    What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

    – Greg Smith
    Jul 25 '10 at 1:24






  • 2





    Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

    – AP257
    Jul 25 '10 at 8:34






  • 82





    DROP SCHEMA public CASCADE; -- shudder

    – wildplasser
    Jun 4 '12 at 20:18








  • 12





    @0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

    – nym
    Jun 9 '15 at 0:04






  • 2





    BTW, when you drop public, you lose any installed extensions.

    – sudo
    Jul 8 '16 at 16:53










2




2





What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

– Greg Smith
Jul 25 '10 at 1:24





What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.

– Greg Smith
Jul 25 '10 at 1:24




2




2





Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

– AP257
Jul 25 '10 at 8:34





Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.

– AP257
Jul 25 '10 at 8:34




82




82





DROP SCHEMA public CASCADE; -- shudder

– wildplasser
Jun 4 '12 at 20:18







DROP SCHEMA public CASCADE; -- shudder

– wildplasser
Jun 4 '12 at 20:18






12




12





@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

– nym
Jun 9 '15 at 0:04





@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)

– nym
Jun 9 '15 at 0:04




2




2





BTW, when you drop public, you lose any installed extensions.

– sudo
Jul 8 '16 at 16:53







BTW, when you drop public, you lose any installed extensions.

– sudo
Jul 8 '16 at 16:53














20 Answers
20






active

oldest

votes


















1112














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;


If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.



GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;





share|improve this answer





















  • 82





    Note that this will also delete all functions, views, etc defined in the public schema.

    – Brad Koch
    Mar 24 '13 at 0:15






  • 3





    Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

    – congusbongus
    Aug 4 '14 at 7:07






  • 29





    This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

    – mgojohn
    Oct 8 '14 at 15:49








  • 13





    Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

    – Federico
    Jan 20 '15 at 23:41






  • 1





    @Federico Why would you want GRANT ALL after the create?

    – 425nesp
    Apr 23 '15 at 3:56



















311














You can write a query to generate a SQL script like this:



select 'drop table "' || tablename || '" cascade;' from pg_tables;


Or:



select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;


In case some tables are automatically dropped due to cascade option in a previous sentence.



Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:



select 'drop table if exists "' || tablename || '" cascade;' 
from pg_tables
where schemaname = 'public'; -- or any other schema


And then run it.



Glorious COPY+PASTE will also work.






share|improve this answer





















  • 7





    I think you meant: You can write a query like this... ...And then run the output of the query

    – Vinko Vrsalovic
    Jul 24 '10 at 23:31






  • 5





    select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

    – Ivo van der Wijk
    Apr 19 '12 at 14:03








  • 11





    the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

    – Guillaume Gendre
    Oct 29 '12 at 16:36






  • 2





    Love it! This is a great bit of stuff.

    – Nicholas DiPiazza
    Jan 26 '13 at 0:40






  • 6





    @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

    – berkes
    May 6 '15 at 14:07



















224














The most accepted answer as of this writing (January 2014) is:



drop schema public cascade;
create schema public;


This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:



-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
AUTHORIZATION postgres;


However, by contrast a brand new database will have the following:



-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
IS 'standard public schema';


For me using a python web framework which creates database tables (web2py), using the former caused problems:



<class 'psycopg2.ProgrammingError'> no schema has been selected to create in 


So to my mind the fully correct answer is:



DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';


(also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)






share|improve this answer



















  • 4





    Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

    – Jinghao Shi
    Sep 15 '14 at 23:42






  • 4





    One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

    – rjh
    Apr 23 '15 at 8:21






  • 2





    This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

    – shacker
    Oct 31 '16 at 20:33





















101














You can drop all tables with



DO $$ DECLARE
r RECORD;
BEGIN
-- if the schema you operate on is not "current", you will want to
-- replace current_schema() in query with 'schematodeletetablesfrom'
-- *and* update the generate 'DROP...' accordingly.
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;


IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.



Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.






share|improve this answer


























  • NB: If you copy-paste to psql add ; at the end

    – igo
    Aug 7 '16 at 8:14













  • @igo, thanks, i've updated the code.

    – Piotr Findeisen
    Aug 9 '16 at 9:43






  • 4





    Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

    – vdboor
    Nov 22 '16 at 10:36








  • 9





    This should be the accepted answer imho. Thanks :)

    – Sankar
    Apr 4 '17 at 8:01











  • Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

    – DPSSpatial
    Nov 18 '17 at 15:46



















75














If everything you want to drop is owned by the same user, then you can use:



drop owned by the_user;


This will drop everything that the user owns.



That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).



You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.



More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html






share|improve this answer





















  • 2





    This dropped all schemas owned by the user (which I didn't want to do).

    – Peter L
    May 7 '18 at 22:37






  • 1





    @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

    – a_horse_with_no_name
    May 8 '18 at 6:11





















63














As per Pablo above, to just drop from a specific schema, with respect to case:



select 'drop table "' || tablename || '" cascade;' 
from pg_tables where schemaname = 'public';





share|improve this answer

































    38














    drop schema public cascade;


    should do the trick.






    share|improve this answer





















    • 10





      Note that this will also delete all functions, views, etc defined in the public schema.

      – Joe Van Dyk
      Jan 9 '13 at 22:23






    • 4





      also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

      – mikermcneil
      Nov 20 '13 at 23:15



















    27














    Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:



    psql -U $PGUSER $PGDB -t -c "select 'drop table "' || tablename || '" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB



    NB: either set or replace $PGUSER and $PGDB with the values you want






    share|improve this answer
























    • Thanks, it works for me.

      – Saeed Zarinfam
      Nov 23 '17 at 9:54



















    19














    If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.



    DROP FUNCTION IF EXISTS remove_all();

    CREATE FUNCTION remove_all() RETURNS void AS $$
    DECLARE
    rec RECORD;
    cmd text;
    BEGIN
    cmd := '';

    FOR rec IN SELECT
    'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
    || quote_ident(c.relname) || ' CASCADE;' AS name
    FROM
    pg_catalog.pg_class AS c
    LEFT JOIN
    pg_catalog.pg_namespace AS n
    ON
    n.oid = c.relnamespace
    WHERE
    relkind = 'S' AND
    n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    pg_catalog.pg_table_is_visible(c.oid)
    LOOP
    cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
    'DROP TABLE ' || quote_ident(n.nspname) || '.'
    || quote_ident(c.relname) || ' CASCADE;' AS name
    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)
    LOOP
    cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
    'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
    || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
    || ');' AS name
    FROM
    pg_proc
    INNER JOIN
    pg_namespace ns
    ON
    (pg_proc.pronamespace = ns.oid)
    WHERE
    ns.nspname =
    'public'
    ORDER BY
    proname
    LOOP
    cmd := cmd || rec.name;
    END LOOP;

    EXECUTE cmd;
    RETURN;
    END;
    $$ LANGUAGE plpgsql;

    SELECT remove_all();


    Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:



    psql -f clean_all_pg.sql


    Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.






    share|improve this answer

































      10














      I modified Pablo's answer slightly for the convenience of having the generated SQL commands returned as one single string:



      select string_agg('drop table "' || tablename || '" cascade', '; ') 
      from pg_tables where schemaname = 'public'





      share|improve this answer































        8














        Just in case... Simple Python script that clean Postgresql database



        import psycopg2
        import sys

        # Drop all tables from a given database

        try:
        conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
        conn.set_isolation_level(0)
        except:
        print "Unable to connect to the database."

        cur = conn.cursor()

        try:
        cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
        rows = cur.fetchall()
        for row in rows:
        print "dropping table: ", row[1]
        cur.execute("drop table " + row[1] + " cascade")
        cur.close()
        conn.close()
        except:
        print "Error: ", sys.exc_info()[1]


        Make sure that after copying it the indentation is right since Python relies on it.






        share|improve this answer



















        • 1





          works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

          – JL Peyret
          Oct 20 '17 at 17:17





















        8














        Use this script in pgAdmin:



        DO $$
        DECLARE
        brow record;
        BEGIN
        FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
        EXECUTE brow.table_name;
        END LOOP;
        END; $$





        share|improve this answer
























        • That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

          – Keith John Hutchison
          May 15 '18 at 11:15











        • What error did you get?

          – Luca Perico
          May 16 '18 at 13:56






        • 1





          Must have been something I did wrong, Luca. I just tried it again and it worked.

          – Keith John Hutchison
          May 17 '18 at 0:31



















        6














        You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE.
        From a bash script:



        #!/bin/bash
        TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

        echo Dropping tables:${TABLES}
        psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"





        share|improve this answer
























        • should should be #!/bin/sh

          – Good Person
          May 11 '14 at 8:04



















        4














        You need to drop tables and sequences, here is what worked for me



        psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
        psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX


        before you run the command you might need to sudo/su to the postgres user or (export connection details PGHOST, PGPORT, PGUSER and PGPASSWORD) and then export PGDATABASE=yourdatabase






        share|improve this answer

































          3














          Rake task for Rails for destroy all tables in current database



          namespace :db do
          # rake db:drop_all_tables
          task drop_all_tables: :environment do
          query = <<-QUERY
          SELECT
          table_name
          FROM
          information_schema.tables
          WHERE
          table_type = 'BASE TABLE'
          AND
          table_schema NOT IN ('pg_catalog', 'information_schema');
          QUERY

          connection = ActiveRecord::Base.connection
          results = connection.execute query

          tables = results.map do |line|
          table_name = line['table_name']
          end.join ", "

          connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
          end
          end





          share|improve this answer



















          • 1





            It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

            – Steve
            Sep 30 '14 at 2:15











          • For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

            – Washington Botelho
            Feb 4 '16 at 14:00



















          3














          If you want delete data (not delete table):



          -- Truncate tables and restart sequnces
          SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
          FROM information_schema.tables
          WHERE table_catalog = '<database>' AND table_schema = '<schema>';


          Or if you want drop table your can use this sql:



          -- For tables
          SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
          FROM information_schema.tables
          WHERE table_catalog = '<database>' AND table_schema = '<schema>';

          -- For sequences
          SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
          FROM information_schema.sequences
          WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';





          share|improve this answer































            2














            I enhanced the bash method from jamie by taking care of views because his only respects the table type "base table" which is the default.



            following bash code deletes the views first and then all the rest



            #!/usr/bin/env bash

            PGDB="yourDB"
            # By exporting user & pass your dont need to interactively type them on execution
            export PGUSER="PGusername"
            export PGPASSWORD="PGpassword"

            VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
            BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

            echo Dropping views:${VIEWS}
            psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
            echo Dropping tables:${BASETBLS}
            psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"





            share|improve this answer
























            • Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

              – raminr
              Jun 14 '15 at 17:53





















            2














            in a Windows batch file:



            @echo off
            FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="dt" YOUR_TABLE_NAME') DO (
            psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
            echo table %%G dropped
            )





            share|improve this answer

































              0














              well, since I like working from the command line...



              psql -U <user> -d <mydb> -c 'dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"



              -c 'dt' will invoke the list tables command.




              List of relations
              Schema | Name | Type | Owner
              --------+-------------------+-------+----------
              public | _d_psidxddlparm | table | djuser
              public | _d_psindexdefn | table | djuser



              cut -d ' ' -f 4 now, pipe its output to grab the 4th field (when using space as separator), which is the table.



              sed is then used to prefix a drop table and suffix the ; command separator.



              | egrep '_d_' - Pipe it into grep some more and you can be more selective about which tables you drop.




              drop table if exists _d_psidxddlparm;
              drop table if exists _d_psindexdefn;



              Note: as written, this will generate bogus rows for the dt commands output of column headers and total rows at the end. I avoid that by grepping, but you could use head and tail.






              share|improve this answer

































                0














                The easiest way is to drop the public schema as others have suggested in previous answers. However, this is NOT a good way. You never know what has been done to the public schema that has since been forgotten and was not documented. You also don't know if this will work the same into the future. In V9, it would have been fine, but in V10 all your users would loose access to the schema, and must be granted access again otherwise your application will break. I haven't checked V11, but the point is that you never know what will break as you move from machine to machine, site to site or version to version. It also cannot be done if you are a user that has access to the database, but not to the schema.



                If you need to do this programmatically then other answers above cover this, but one thing the answers above don't consider is to get Postgres to do the work for you. If you use pg_dump with the -c option as below:



                sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""


                That will create a DB restore script with sql statements that will delete all the tables.



                If the only purpose in asking the question was to delete the tables prior to restore, then your restore will do the work for you.



                However, if you need it for something else, you can simply copy the drop statements from the sql script.






                share|improve this answer























                  Your Answer






                  StackExchange.ifUsing("editor", function () {
                  StackExchange.using("externalEditor", function () {
                  StackExchange.using("snippets", function () {
                  StackExchange.snippets.init();
                  });
                  });
                  }, "code-snippets");

                  StackExchange.ready(function() {
                  var channelOptions = {
                  tags: "".split(" "),
                  id: "1"
                  };
                  initTagRenderer("".split(" "), "".split(" "), channelOptions);

                  StackExchange.using("externalEditor", function() {
                  // Have to fire editor after snippets, if snippets enabled
                  if (StackExchange.settings.snippets.snippetsEnabled) {
                  StackExchange.using("snippets", function() {
                  createEditor();
                  });
                  }
                  else {
                  createEditor();
                  }
                  });

                  function createEditor() {
                  StackExchange.prepareEditor({
                  heartbeatType: 'answer',
                  autoActivateHeartbeat: false,
                  convertImagesToLinks: true,
                  noModals: true,
                  showLowRepImageUploadWarning: true,
                  reputationToPostImages: 10,
                  bindNavPrevention: true,
                  postfix: "",
                  imageUploader: {
                  brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                  contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                  allowUrls: true
                  },
                  onDemand: true,
                  discardSelector: ".discard-answer"
                  ,immediatelyShowMarkdownHelp:true
                  });


                  }
                  });














                  draft saved

                  draft discarded


















                  StackExchange.ready(
                  function () {
                  StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3327312%2fdrop-all-tables-in-postgresql%23new-answer', 'question_page');
                  }
                  );

                  Post as a guest















                  Required, but never shown

























                  20 Answers
                  20






                  active

                  oldest

                  votes








                  20 Answers
                  20






                  active

                  oldest

                  votes









                  active

                  oldest

                  votes






                  active

                  oldest

                  votes









                  1112














                  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;


                  If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.



                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;





                  share|improve this answer





















                  • 82





                    Note that this will also delete all functions, views, etc defined in the public schema.

                    – Brad Koch
                    Mar 24 '13 at 0:15






                  • 3





                    Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                    – congusbongus
                    Aug 4 '14 at 7:07






                  • 29





                    This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                    – mgojohn
                    Oct 8 '14 at 15:49








                  • 13





                    Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                    – Federico
                    Jan 20 '15 at 23:41






                  • 1





                    @Federico Why would you want GRANT ALL after the create?

                    – 425nesp
                    Apr 23 '15 at 3:56
















                  1112














                  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;


                  If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.



                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;





                  share|improve this answer





















                  • 82





                    Note that this will also delete all functions, views, etc defined in the public schema.

                    – Brad Koch
                    Mar 24 '13 at 0:15






                  • 3





                    Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                    – congusbongus
                    Aug 4 '14 at 7:07






                  • 29





                    This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                    – mgojohn
                    Oct 8 '14 at 15:49








                  • 13





                    Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                    – Federico
                    Jan 20 '15 at 23:41






                  • 1





                    @Federico Why would you want GRANT ALL after the create?

                    – 425nesp
                    Apr 23 '15 at 3:56














                  1112












                  1112








                  1112







                  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;


                  If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.



                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;





                  share|improve this answer















                  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;


                  If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.



                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Oct 19 '16 at 9:45









                  biniam

                  5,97833345




                  5,97833345










                  answered Dec 11 '12 at 15:52









                  Derek SlifeDerek Slife

                  12.4k11221




                  12.4k11221








                  • 82





                    Note that this will also delete all functions, views, etc defined in the public schema.

                    – Brad Koch
                    Mar 24 '13 at 0:15






                  • 3





                    Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                    – congusbongus
                    Aug 4 '14 at 7:07






                  • 29





                    This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                    – mgojohn
                    Oct 8 '14 at 15:49








                  • 13





                    Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                    – Federico
                    Jan 20 '15 at 23:41






                  • 1





                    @Federico Why would you want GRANT ALL after the create?

                    – 425nesp
                    Apr 23 '15 at 3:56














                  • 82





                    Note that this will also delete all functions, views, etc defined in the public schema.

                    – Brad Koch
                    Mar 24 '13 at 0:15






                  • 3





                    Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                    – congusbongus
                    Aug 4 '14 at 7:07






                  • 29





                    This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                    – mgojohn
                    Oct 8 '14 at 15:49








                  • 13





                    Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                    – Federico
                    Jan 20 '15 at 23:41






                  • 1





                    @Federico Why would you want GRANT ALL after the create?

                    – 425nesp
                    Apr 23 '15 at 3:56








                  82




                  82





                  Note that this will also delete all functions, views, etc defined in the public schema.

                  – Brad Koch
                  Mar 24 '13 at 0:15





                  Note that this will also delete all functions, views, etc defined in the public schema.

                  – Brad Koch
                  Mar 24 '13 at 0:15




                  3




                  3





                  Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                  – congusbongus
                  Aug 4 '14 at 7:07





                  Note that this will not remove the system tables (such as those that begin with pg_) as they are in a different schema, pg_catalog.

                  – congusbongus
                  Aug 4 '14 at 7:07




                  29




                  29





                  This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                  – mgojohn
                  Oct 8 '14 at 15:49







                  This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)

                  – mgojohn
                  Oct 8 '14 at 15:49






                  13




                  13





                  Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                  – Federico
                  Jan 20 '15 at 23:41





                  Bringing this up from another answer you probably want to have a GRANT ALL ON SCHEMA public TO public; after the create.

                  – Federico
                  Jan 20 '15 at 23:41




                  1




                  1





                  @Federico Why would you want GRANT ALL after the create?

                  – 425nesp
                  Apr 23 '15 at 3:56





                  @Federico Why would you want GRANT ALL after the create?

                  – 425nesp
                  Apr 23 '15 at 3:56













                  311














                  You can write a query to generate a SQL script like this:



                  select 'drop table "' || tablename || '" cascade;' from pg_tables;


                  Or:



                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;


                  In case some tables are automatically dropped due to cascade option in a previous sentence.



                  Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:



                  select 'drop table if exists "' || tablename || '" cascade;' 
                  from pg_tables
                  where schemaname = 'public'; -- or any other schema


                  And then run it.



                  Glorious COPY+PASTE will also work.






                  share|improve this answer





















                  • 7





                    I think you meant: You can write a query like this... ...And then run the output of the query

                    – Vinko Vrsalovic
                    Jul 24 '10 at 23:31






                  • 5





                    select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                    – Ivo van der Wijk
                    Apr 19 '12 at 14:03








                  • 11





                    the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                    – Guillaume Gendre
                    Oct 29 '12 at 16:36






                  • 2





                    Love it! This is a great bit of stuff.

                    – Nicholas DiPiazza
                    Jan 26 '13 at 0:40






                  • 6





                    @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                    – berkes
                    May 6 '15 at 14:07
















                  311














                  You can write a query to generate a SQL script like this:



                  select 'drop table "' || tablename || '" cascade;' from pg_tables;


                  Or:



                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;


                  In case some tables are automatically dropped due to cascade option in a previous sentence.



                  Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:



                  select 'drop table if exists "' || tablename || '" cascade;' 
                  from pg_tables
                  where schemaname = 'public'; -- or any other schema


                  And then run it.



                  Glorious COPY+PASTE will also work.






                  share|improve this answer





















                  • 7





                    I think you meant: You can write a query like this... ...And then run the output of the query

                    – Vinko Vrsalovic
                    Jul 24 '10 at 23:31






                  • 5





                    select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                    – Ivo van der Wijk
                    Apr 19 '12 at 14:03








                  • 11





                    the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                    – Guillaume Gendre
                    Oct 29 '12 at 16:36






                  • 2





                    Love it! This is a great bit of stuff.

                    – Nicholas DiPiazza
                    Jan 26 '13 at 0:40






                  • 6





                    @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                    – berkes
                    May 6 '15 at 14:07














                  311












                  311








                  311







                  You can write a query to generate a SQL script like this:



                  select 'drop table "' || tablename || '" cascade;' from pg_tables;


                  Or:



                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;


                  In case some tables are automatically dropped due to cascade option in a previous sentence.



                  Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:



                  select 'drop table if exists "' || tablename || '" cascade;' 
                  from pg_tables
                  where schemaname = 'public'; -- or any other schema


                  And then run it.



                  Glorious COPY+PASTE will also work.






                  share|improve this answer















                  You can write a query to generate a SQL script like this:



                  select 'drop table "' || tablename || '" cascade;' from pg_tables;


                  Or:



                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;


                  In case some tables are automatically dropped due to cascade option in a previous sentence.



                  Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:



                  select 'drop table if exists "' || tablename || '" cascade;' 
                  from pg_tables
                  where schemaname = 'public'; -- or any other schema


                  And then run it.



                  Glorious COPY+PASTE will also work.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Oct 29 '12 at 17:03

























                  answered Jul 24 '10 at 23:29









                  Pablo Santa CruzPablo Santa Cruz

                  133k24198251




                  133k24198251








                  • 7





                    I think you meant: You can write a query like this... ...And then run the output of the query

                    – Vinko Vrsalovic
                    Jul 24 '10 at 23:31






                  • 5





                    select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                    – Ivo van der Wijk
                    Apr 19 '12 at 14:03








                  • 11





                    the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                    – Guillaume Gendre
                    Oct 29 '12 at 16:36






                  • 2





                    Love it! This is a great bit of stuff.

                    – Nicholas DiPiazza
                    Jan 26 '13 at 0:40






                  • 6





                    @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                    – berkes
                    May 6 '15 at 14:07














                  • 7





                    I think you meant: You can write a query like this... ...And then run the output of the query

                    – Vinko Vrsalovic
                    Jul 24 '10 at 23:31






                  • 5





                    select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                    – Ivo van der Wijk
                    Apr 19 '12 at 14:03








                  • 11





                    the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                    – Guillaume Gendre
                    Oct 29 '12 at 16:36






                  • 2





                    Love it! This is a great bit of stuff.

                    – Nicholas DiPiazza
                    Jan 26 '13 at 0:40






                  • 6





                    @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                    – berkes
                    May 6 '15 at 14:07








                  7




                  7





                  I think you meant: You can write a query like this... ...And then run the output of the query

                  – Vinko Vrsalovic
                  Jul 24 '10 at 23:31





                  I think you meant: You can write a query like this... ...And then run the output of the query

                  – Vinko Vrsalovic
                  Jul 24 '10 at 23:31




                  5




                  5





                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                  – Ivo van der Wijk
                  Apr 19 '12 at 14:03







                  select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.

                  – Ivo van der Wijk
                  Apr 19 '12 at 14:03






                  11




                  11





                  the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                  – Guillaume Gendre
                  Oct 29 '12 at 16:36





                  the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones

                  – Guillaume Gendre
                  Oct 29 '12 at 16:36




                  2




                  2





                  Love it! This is a great bit of stuff.

                  – Nicholas DiPiazza
                  Jan 26 '13 at 0:40





                  Love it! This is a great bit of stuff.

                  – Nicholas DiPiazza
                  Jan 26 '13 at 0:40




                  6




                  6





                  @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                  – berkes
                  May 6 '15 at 14:07





                  @jwg: also, because sometimes you don't have the permission to drop schema public cascade;, but you almost always have the permissions to drop tables.

                  – berkes
                  May 6 '15 at 14:07











                  224














                  The most accepted answer as of this writing (January 2014) is:



                  drop schema public cascade;
                  create schema public;


                  This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;


                  However, by contrast a brand new database will have the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;

                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public
                  IS 'standard public schema';


                  For me using a python web framework which creates database tables (web2py), using the former caused problems:



                  <class 'psycopg2.ProgrammingError'> no schema has been selected to create in 


                  So to my mind the fully correct answer is:



                  DROP SCHEMA public CASCADE;
                  CREATE SCHEMA public;
                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public IS 'standard public schema';


                  (also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)






                  share|improve this answer



















                  • 4





                    Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                    – Jinghao Shi
                    Sep 15 '14 at 23:42






                  • 4





                    One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                    – rjh
                    Apr 23 '15 at 8:21






                  • 2





                    This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                    – shacker
                    Oct 31 '16 at 20:33


















                  224














                  The most accepted answer as of this writing (January 2014) is:



                  drop schema public cascade;
                  create schema public;


                  This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;


                  However, by contrast a brand new database will have the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;

                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public
                  IS 'standard public schema';


                  For me using a python web framework which creates database tables (web2py), using the former caused problems:



                  <class 'psycopg2.ProgrammingError'> no schema has been selected to create in 


                  So to my mind the fully correct answer is:



                  DROP SCHEMA public CASCADE;
                  CREATE SCHEMA public;
                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public IS 'standard public schema';


                  (also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)






                  share|improve this answer



















                  • 4





                    Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                    – Jinghao Shi
                    Sep 15 '14 at 23:42






                  • 4





                    One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                    – rjh
                    Apr 23 '15 at 8:21






                  • 2





                    This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                    – shacker
                    Oct 31 '16 at 20:33
















                  224












                  224








                  224







                  The most accepted answer as of this writing (January 2014) is:



                  drop schema public cascade;
                  create schema public;


                  This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;


                  However, by contrast a brand new database will have the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;

                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public
                  IS 'standard public schema';


                  For me using a python web framework which creates database tables (web2py), using the former caused problems:



                  <class 'psycopg2.ProgrammingError'> no schema has been selected to create in 


                  So to my mind the fully correct answer is:



                  DROP SCHEMA public CASCADE;
                  CREATE SCHEMA public;
                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public IS 'standard public schema';


                  (also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)






                  share|improve this answer













                  The most accepted answer as of this writing (January 2014) is:



                  drop schema public cascade;
                  create schema public;


                  This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;


                  However, by contrast a brand new database will have the following:



                  -- Schema: public

                  -- DROP SCHEMA public;

                  CREATE SCHEMA public
                  AUTHORIZATION postgres;

                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public
                  IS 'standard public schema';


                  For me using a python web framework which creates database tables (web2py), using the former caused problems:



                  <class 'psycopg2.ProgrammingError'> no schema has been selected to create in 


                  So to my mind the fully correct answer is:



                  DROP SCHEMA public CASCADE;
                  CREATE SCHEMA public;
                  GRANT ALL ON SCHEMA public TO postgres;
                  GRANT ALL ON SCHEMA public TO public;
                  COMMENT ON SCHEMA public IS 'standard public schema';


                  (also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 21 '14 at 0:38









                  UserUser

                  23.9k51139219




                  23.9k51139219








                  • 4





                    Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                    – Jinghao Shi
                    Sep 15 '14 at 23:42






                  • 4





                    One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                    – rjh
                    Apr 23 '15 at 8:21






                  • 2





                    This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                    – shacker
                    Oct 31 '16 at 20:33
















                  • 4





                    Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                    – Jinghao Shi
                    Sep 15 '14 at 23:42






                  • 4





                    One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                    – rjh
                    Apr 23 '15 at 8:21






                  • 2





                    This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                    – shacker
                    Oct 31 '16 at 20:33










                  4




                  4





                  Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                  – Jinghao Shi
                  Sep 15 '14 at 23:42





                  Confirmed. The two line solution (drop then create) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant is necessary.

                  – Jinghao Shi
                  Sep 15 '14 at 23:42




                  4




                  4





                  One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                  – rjh
                  Apr 23 '15 at 8:21





                  One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.

                  – rjh
                  Apr 23 '15 at 8:21




                  2




                  2





                  This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                  – shacker
                  Oct 31 '16 at 20:33







                  This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;

                  – shacker
                  Oct 31 '16 at 20:33













                  101














                  You can drop all tables with



                  DO $$ DECLARE
                  r RECORD;
                  BEGIN
                  -- if the schema you operate on is not "current", you will want to
                  -- replace current_schema() in query with 'schematodeletetablesfrom'
                  -- *and* update the generate 'DROP...' accordingly.
                  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
                  EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
                  END LOOP;
                  END $$;


                  IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.



                  Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.






                  share|improve this answer


























                  • NB: If you copy-paste to psql add ; at the end

                    – igo
                    Aug 7 '16 at 8:14













                  • @igo, thanks, i've updated the code.

                    – Piotr Findeisen
                    Aug 9 '16 at 9:43






                  • 4





                    Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                    – vdboor
                    Nov 22 '16 at 10:36








                  • 9





                    This should be the accepted answer imho. Thanks :)

                    – Sankar
                    Apr 4 '17 at 8:01











                  • Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                    – DPSSpatial
                    Nov 18 '17 at 15:46
















                  101














                  You can drop all tables with



                  DO $$ DECLARE
                  r RECORD;
                  BEGIN
                  -- if the schema you operate on is not "current", you will want to
                  -- replace current_schema() in query with 'schematodeletetablesfrom'
                  -- *and* update the generate 'DROP...' accordingly.
                  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
                  EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
                  END LOOP;
                  END $$;


                  IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.



                  Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.






                  share|improve this answer


























                  • NB: If you copy-paste to psql add ; at the end

                    – igo
                    Aug 7 '16 at 8:14













                  • @igo, thanks, i've updated the code.

                    – Piotr Findeisen
                    Aug 9 '16 at 9:43






                  • 4





                    Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                    – vdboor
                    Nov 22 '16 at 10:36








                  • 9





                    This should be the accepted answer imho. Thanks :)

                    – Sankar
                    Apr 4 '17 at 8:01











                  • Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                    – DPSSpatial
                    Nov 18 '17 at 15:46














                  101












                  101








                  101







                  You can drop all tables with



                  DO $$ DECLARE
                  r RECORD;
                  BEGIN
                  -- if the schema you operate on is not "current", you will want to
                  -- replace current_schema() in query with 'schematodeletetablesfrom'
                  -- *and* update the generate 'DROP...' accordingly.
                  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
                  EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
                  END LOOP;
                  END $$;


                  IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.



                  Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.






                  share|improve this answer















                  You can drop all tables with



                  DO $$ DECLARE
                  r RECORD;
                  BEGIN
                  -- if the schema you operate on is not "current", you will want to
                  -- replace current_schema() in query with 'schematodeletetablesfrom'
                  -- *and* update the generate 'DROP...' accordingly.
                  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
                  EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
                  END LOOP;
                  END $$;


                  IMO this is better than drop schema public, because you don't need to recreate the schema and restore all the grants.



                  Additional bonus that this doesn't require external scripting language, nor copy-pasting of generated SQL back to the interpreter.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 9 '16 at 9:43

























                  answered Mar 15 '16 at 22:21









                  Piotr FindeisenPiotr Findeisen

                  5,06611538




                  5,06611538













                  • NB: If you copy-paste to psql add ; at the end

                    – igo
                    Aug 7 '16 at 8:14













                  • @igo, thanks, i've updated the code.

                    – Piotr Findeisen
                    Aug 9 '16 at 9:43






                  • 4





                    Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                    – vdboor
                    Nov 22 '16 at 10:36








                  • 9





                    This should be the accepted answer imho. Thanks :)

                    – Sankar
                    Apr 4 '17 at 8:01











                  • Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                    – DPSSpatial
                    Nov 18 '17 at 15:46



















                  • NB: If you copy-paste to psql add ; at the end

                    – igo
                    Aug 7 '16 at 8:14













                  • @igo, thanks, i've updated the code.

                    – Piotr Findeisen
                    Aug 9 '16 at 9:43






                  • 4





                    Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                    – vdboor
                    Nov 22 '16 at 10:36








                  • 9





                    This should be the accepted answer imho. Thanks :)

                    – Sankar
                    Apr 4 '17 at 8:01











                  • Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                    – DPSSpatial
                    Nov 18 '17 at 15:46

















                  NB: If you copy-paste to psql add ; at the end

                  – igo
                  Aug 7 '16 at 8:14







                  NB: If you copy-paste to psql add ; at the end

                  – igo
                  Aug 7 '16 at 8:14















                  @igo, thanks, i've updated the code.

                  – Piotr Findeisen
                  Aug 9 '16 at 9:43





                  @igo, thanks, i've updated the code.

                  – Piotr Findeisen
                  Aug 9 '16 at 9:43




                  4




                  4





                  Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                  – vdboor
                  Nov 22 '16 at 10:36







                  Thanks for posting this! I couldn't use the drop schema trick as the user was not owner of the schema, only of the tables. This one worked though :)

                  – vdboor
                  Nov 22 '16 at 10:36






                  9




                  9





                  This should be the accepted answer imho. Thanks :)

                  – Sankar
                  Apr 4 '17 at 8:01





                  This should be the accepted answer imho. Thanks :)

                  – Sankar
                  Apr 4 '17 at 8:01













                  Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                  – DPSSpatial
                  Nov 18 '17 at 15:46





                  Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...

                  – DPSSpatial
                  Nov 18 '17 at 15:46











                  75














                  If everything you want to drop is owned by the same user, then you can use:



                  drop owned by the_user;


                  This will drop everything that the user owns.



                  That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).



                  You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.



                  More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html






                  share|improve this answer





















                  • 2





                    This dropped all schemas owned by the user (which I didn't want to do).

                    – Peter L
                    May 7 '18 at 22:37






                  • 1





                    @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                    – a_horse_with_no_name
                    May 8 '18 at 6:11


















                  75














                  If everything you want to drop is owned by the same user, then you can use:



                  drop owned by the_user;


                  This will drop everything that the user owns.



                  That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).



                  You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.



                  More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html






                  share|improve this answer





















                  • 2





                    This dropped all schemas owned by the user (which I didn't want to do).

                    – Peter L
                    May 7 '18 at 22:37






                  • 1





                    @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                    – a_horse_with_no_name
                    May 8 '18 at 6:11
















                  75












                  75








                  75







                  If everything you want to drop is owned by the same user, then you can use:



                  drop owned by the_user;


                  This will drop everything that the user owns.



                  That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).



                  You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.



                  More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html






                  share|improve this answer















                  If everything you want to drop is owned by the same user, then you can use:



                  drop owned by the_user;


                  This will drop everything that the user owns.



                  That includes materialized views, views, sequences, triggers, schemas, functions, types, aggregates, operators, domains and so on (so, really: everything) that the_user owns (=created).



                  You have to replace the_user with the actual username, currently there is no option to drop everything for "the current user". The upcoming 9.5 version will have the option drop owned by current_user.



                  More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Sep 17 '18 at 18:33

























                  answered Dec 1 '15 at 7:17









                  a_horse_with_no_namea_horse_with_no_name

                  294k46449543




                  294k46449543








                  • 2





                    This dropped all schemas owned by the user (which I didn't want to do).

                    – Peter L
                    May 7 '18 at 22:37






                  • 1





                    @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                    – a_horse_with_no_name
                    May 8 '18 at 6:11
















                  • 2





                    This dropped all schemas owned by the user (which I didn't want to do).

                    – Peter L
                    May 7 '18 at 22:37






                  • 1





                    @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                    – a_horse_with_no_name
                    May 8 '18 at 6:11










                  2




                  2





                  This dropped all schemas owned by the user (which I didn't want to do).

                  – Peter L
                  May 7 '18 at 22:37





                  This dropped all schemas owned by the user (which I didn't want to do).

                  – Peter L
                  May 7 '18 at 22:37




                  1




                  1





                  @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                  – a_horse_with_no_name
                  May 8 '18 at 6:11







                  @PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything

                  – a_horse_with_no_name
                  May 8 '18 at 6:11













                  63














                  As per Pablo above, to just drop from a specific schema, with respect to case:



                  select 'drop table "' || tablename || '" cascade;' 
                  from pg_tables where schemaname = 'public';





                  share|improve this answer






























                    63














                    As per Pablo above, to just drop from a specific schema, with respect to case:



                    select 'drop table "' || tablename || '" cascade;' 
                    from pg_tables where schemaname = 'public';





                    share|improve this answer




























                      63












                      63








                      63







                      As per Pablo above, to just drop from a specific schema, with respect to case:



                      select 'drop table "' || tablename || '" cascade;' 
                      from pg_tables where schemaname = 'public';





                      share|improve this answer















                      As per Pablo above, to just drop from a specific schema, with respect to case:



                      select 'drop table "' || tablename || '" cascade;' 
                      from pg_tables where schemaname = 'public';






                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited Jun 4 '12 at 20:15









                      MAbraham1

                      1,11842135




                      1,11842135










                      answered May 1 '12 at 6:27









                      LenWLenW

                      2,33311924




                      2,33311924























                          38














                          drop schema public cascade;


                          should do the trick.






                          share|improve this answer





















                          • 10





                            Note that this will also delete all functions, views, etc defined in the public schema.

                            – Joe Van Dyk
                            Jan 9 '13 at 22:23






                          • 4





                            also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                            – mikermcneil
                            Nov 20 '13 at 23:15
















                          38














                          drop schema public cascade;


                          should do the trick.






                          share|improve this answer





















                          • 10





                            Note that this will also delete all functions, views, etc defined in the public schema.

                            – Joe Van Dyk
                            Jan 9 '13 at 22:23






                          • 4





                            also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                            – mikermcneil
                            Nov 20 '13 at 23:15














                          38












                          38








                          38







                          drop schema public cascade;


                          should do the trick.






                          share|improve this answer















                          drop schema public cascade;


                          should do the trick.







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Oct 28 '12 at 1:17

























                          answered Oct 27 '12 at 21:11









                          Joe Van DykJoe Van Dyk

                          4,65274567




                          4,65274567








                          • 10





                            Note that this will also delete all functions, views, etc defined in the public schema.

                            – Joe Van Dyk
                            Jan 9 '13 at 22:23






                          • 4





                            also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                            – mikermcneil
                            Nov 20 '13 at 23:15














                          • 10





                            Note that this will also delete all functions, views, etc defined in the public schema.

                            – Joe Van Dyk
                            Jan 9 '13 at 22:23






                          • 4





                            also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                            – mikermcneil
                            Nov 20 '13 at 23:15








                          10




                          10





                          Note that this will also delete all functions, views, etc defined in the public schema.

                          – Joe Van Dyk
                          Jan 9 '13 at 22:23





                          Note that this will also delete all functions, views, etc defined in the public schema.

                          – Joe Van Dyk
                          Jan 9 '13 at 22:23




                          4




                          4





                          also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                          – mikermcneil
                          Nov 20 '13 at 23:15





                          also you'll have to recreated again afterwards to add the tables back with CREATE SCHEMA public;. Also see stackoverflow.com/a/14286370 for more information

                          – mikermcneil
                          Nov 20 '13 at 23:15











                          27














                          Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:



                          psql -U $PGUSER $PGDB -t -c "select 'drop table "' || tablename || '" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB



                          NB: either set or replace $PGUSER and $PGDB with the values you want






                          share|improve this answer
























                          • Thanks, it works for me.

                            – Saeed Zarinfam
                            Nov 23 '17 at 9:54
















                          27














                          Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:



                          psql -U $PGUSER $PGDB -t -c "select 'drop table "' || tablename || '" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB



                          NB: either set or replace $PGUSER and $PGDB with the values you want






                          share|improve this answer
























                          • Thanks, it works for me.

                            – Saeed Zarinfam
                            Nov 23 '17 at 9:54














                          27












                          27








                          27







                          Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:



                          psql -U $PGUSER $PGDB -t -c "select 'drop table "' || tablename || '" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB



                          NB: either set or replace $PGUSER and $PGDB with the values you want






                          share|improve this answer













                          Following Pablo and LenW, here's a one-liner that does it all both preparing and then executing:



                          psql -U $PGUSER $PGDB -t -c "select 'drop table "' || tablename || '" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB



                          NB: either set or replace $PGUSER and $PGDB with the values you want







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Oct 23 '12 at 15:03









                          Tim DigginsTim Diggins

                          3,15032139




                          3,15032139













                          • Thanks, it works for me.

                            – Saeed Zarinfam
                            Nov 23 '17 at 9:54



















                          • Thanks, it works for me.

                            – Saeed Zarinfam
                            Nov 23 '17 at 9:54

















                          Thanks, it works for me.

                          – Saeed Zarinfam
                          Nov 23 '17 at 9:54





                          Thanks, it works for me.

                          – Saeed Zarinfam
                          Nov 23 '17 at 9:54











                          19














                          If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.



                          DROP FUNCTION IF EXISTS remove_all();

                          CREATE FUNCTION remove_all() RETURNS void AS $$
                          DECLARE
                          rec RECORD;
                          cmd text;
                          BEGIN
                          cmd := '';

                          FOR rec IN SELECT
                          'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                          || quote_ident(c.relname) || ' CASCADE;' AS name
                          FROM
                          pg_catalog.pg_class AS c
                          LEFT JOIN
                          pg_catalog.pg_namespace AS n
                          ON
                          n.oid = c.relnamespace
                          WHERE
                          relkind = 'S' AND
                          n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
                          pg_catalog.pg_table_is_visible(c.oid)
                          LOOP
                          cmd := cmd || rec.name;
                          END LOOP;

                          FOR rec IN SELECT
                          'DROP TABLE ' || quote_ident(n.nspname) || '.'
                          || quote_ident(c.relname) || ' CASCADE;' AS name
                          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)
                          LOOP
                          cmd := cmd || rec.name;
                          END LOOP;

                          FOR rec IN SELECT
                          'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                          || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                          || ');' AS name
                          FROM
                          pg_proc
                          INNER JOIN
                          pg_namespace ns
                          ON
                          (pg_proc.pronamespace = ns.oid)
                          WHERE
                          ns.nspname =
                          'public'
                          ORDER BY
                          proname
                          LOOP
                          cmd := cmd || rec.name;
                          END LOOP;

                          EXECUTE cmd;
                          RETURN;
                          END;
                          $$ LANGUAGE plpgsql;

                          SELECT remove_all();


                          Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:



                          psql -f clean_all_pg.sql


                          Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.






                          share|improve this answer






























                            19














                            If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.



                            DROP FUNCTION IF EXISTS remove_all();

                            CREATE FUNCTION remove_all() RETURNS void AS $$
                            DECLARE
                            rec RECORD;
                            cmd text;
                            BEGIN
                            cmd := '';

                            FOR rec IN SELECT
                            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                            || quote_ident(c.relname) || ' CASCADE;' AS name
                            FROM
                            pg_catalog.pg_class AS c
                            LEFT JOIN
                            pg_catalog.pg_namespace AS n
                            ON
                            n.oid = c.relnamespace
                            WHERE
                            relkind = 'S' AND
                            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
                            pg_catalog.pg_table_is_visible(c.oid)
                            LOOP
                            cmd := cmd || rec.name;
                            END LOOP;

                            FOR rec IN SELECT
                            'DROP TABLE ' || quote_ident(n.nspname) || '.'
                            || quote_ident(c.relname) || ' CASCADE;' AS name
                            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)
                            LOOP
                            cmd := cmd || rec.name;
                            END LOOP;

                            FOR rec IN SELECT
                            'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                            || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                            || ');' AS name
                            FROM
                            pg_proc
                            INNER JOIN
                            pg_namespace ns
                            ON
                            (pg_proc.pronamespace = ns.oid)
                            WHERE
                            ns.nspname =
                            'public'
                            ORDER BY
                            proname
                            LOOP
                            cmd := cmd || rec.name;
                            END LOOP;

                            EXECUTE cmd;
                            RETURN;
                            END;
                            $$ LANGUAGE plpgsql;

                            SELECT remove_all();


                            Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:



                            psql -f clean_all_pg.sql


                            Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.






                            share|improve this answer




























                              19












                              19








                              19







                              If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.



                              DROP FUNCTION IF EXISTS remove_all();

                              CREATE FUNCTION remove_all() RETURNS void AS $$
                              DECLARE
                              rec RECORD;
                              cmd text;
                              BEGIN
                              cmd := '';

                              FOR rec IN SELECT
                              'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                              || quote_ident(c.relname) || ' CASCADE;' AS name
                              FROM
                              pg_catalog.pg_class AS c
                              LEFT JOIN
                              pg_catalog.pg_namespace AS n
                              ON
                              n.oid = c.relnamespace
                              WHERE
                              relkind = 'S' AND
                              n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
                              pg_catalog.pg_table_is_visible(c.oid)
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              FOR rec IN SELECT
                              'DROP TABLE ' || quote_ident(n.nspname) || '.'
                              || quote_ident(c.relname) || ' CASCADE;' AS name
                              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)
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              FOR rec IN SELECT
                              'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                              || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                              || ');' AS name
                              FROM
                              pg_proc
                              INNER JOIN
                              pg_namespace ns
                              ON
                              (pg_proc.pronamespace = ns.oid)
                              WHERE
                              ns.nspname =
                              'public'
                              ORDER BY
                              proname
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              EXECUTE cmd;
                              RETURN;
                              END;
                              $$ LANGUAGE plpgsql;

                              SELECT remove_all();


                              Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:



                              psql -f clean_all_pg.sql


                              Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.






                              share|improve this answer















                              If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.



                              DROP FUNCTION IF EXISTS remove_all();

                              CREATE FUNCTION remove_all() RETURNS void AS $$
                              DECLARE
                              rec RECORD;
                              cmd text;
                              BEGIN
                              cmd := '';

                              FOR rec IN SELECT
                              'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                              || quote_ident(c.relname) || ' CASCADE;' AS name
                              FROM
                              pg_catalog.pg_class AS c
                              LEFT JOIN
                              pg_catalog.pg_namespace AS n
                              ON
                              n.oid = c.relnamespace
                              WHERE
                              relkind = 'S' AND
                              n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
                              pg_catalog.pg_table_is_visible(c.oid)
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              FOR rec IN SELECT
                              'DROP TABLE ' || quote_ident(n.nspname) || '.'
                              || quote_ident(c.relname) || ' CASCADE;' AS name
                              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)
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              FOR rec IN SELECT
                              'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                              || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                              || ');' AS name
                              FROM
                              pg_proc
                              INNER JOIN
                              pg_namespace ns
                              ON
                              (pg_proc.pronamespace = ns.oid)
                              WHERE
                              ns.nspname =
                              'public'
                              ORDER BY
                              proname
                              LOOP
                              cmd := cmd || rec.name;
                              END LOOP;

                              EXECUTE cmd;
                              RETURN;
                              END;
                              $$ LANGUAGE plpgsql;

                              SELECT remove_all();


                              Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:



                              psql -f clean_all_pg.sql


                              Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.







                              share|improve this answer














                              share|improve this answer



                              share|improve this answer








                              edited Jul 16 '12 at 3:13

























                              answered Jul 13 '12 at 0:12









                              Mark LawrenceMark Lawrence

                              23126




                              23126























                                  10














                                  I modified Pablo's answer slightly for the convenience of having the generated SQL commands returned as one single string:



                                  select string_agg('drop table "' || tablename || '" cascade', '; ') 
                                  from pg_tables where schemaname = 'public'





                                  share|improve this answer




























                                    10














                                    I modified Pablo's answer slightly for the convenience of having the generated SQL commands returned as one single string:



                                    select string_agg('drop table "' || tablename || '" cascade', '; ') 
                                    from pg_tables where schemaname = 'public'





                                    share|improve this answer


























                                      10












                                      10








                                      10







                                      I modified Pablo's answer slightly for the convenience of having the generated SQL commands returned as one single string:



                                      select string_agg('drop table "' || tablename || '" cascade', '; ') 
                                      from pg_tables where schemaname = 'public'





                                      share|improve this answer













                                      I modified Pablo's answer slightly for the convenience of having the generated SQL commands returned as one single string:



                                      select string_agg('drop table "' || tablename || '" cascade', '; ') 
                                      from pg_tables where schemaname = 'public'






                                      share|improve this answer












                                      share|improve this answer



                                      share|improve this answer










                                      answered Jul 7 '17 at 23:38









                                      AdéAdé

                                      14029




                                      14029























                                          8














                                          Just in case... Simple Python script that clean Postgresql database



                                          import psycopg2
                                          import sys

                                          # Drop all tables from a given database

                                          try:
                                          conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
                                          conn.set_isolation_level(0)
                                          except:
                                          print "Unable to connect to the database."

                                          cur = conn.cursor()

                                          try:
                                          cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
                                          rows = cur.fetchall()
                                          for row in rows:
                                          print "dropping table: ", row[1]
                                          cur.execute("drop table " + row[1] + " cascade")
                                          cur.close()
                                          conn.close()
                                          except:
                                          print "Error: ", sys.exc_info()[1]


                                          Make sure that after copying it the indentation is right since Python relies on it.






                                          share|improve this answer



















                                          • 1





                                            works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                            – JL Peyret
                                            Oct 20 '17 at 17:17


















                                          8














                                          Just in case... Simple Python script that clean Postgresql database



                                          import psycopg2
                                          import sys

                                          # Drop all tables from a given database

                                          try:
                                          conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
                                          conn.set_isolation_level(0)
                                          except:
                                          print "Unable to connect to the database."

                                          cur = conn.cursor()

                                          try:
                                          cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
                                          rows = cur.fetchall()
                                          for row in rows:
                                          print "dropping table: ", row[1]
                                          cur.execute("drop table " + row[1] + " cascade")
                                          cur.close()
                                          conn.close()
                                          except:
                                          print "Error: ", sys.exc_info()[1]


                                          Make sure that after copying it the indentation is right since Python relies on it.






                                          share|improve this answer



















                                          • 1





                                            works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                            – JL Peyret
                                            Oct 20 '17 at 17:17
















                                          8












                                          8








                                          8







                                          Just in case... Simple Python script that clean Postgresql database



                                          import psycopg2
                                          import sys

                                          # Drop all tables from a given database

                                          try:
                                          conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
                                          conn.set_isolation_level(0)
                                          except:
                                          print "Unable to connect to the database."

                                          cur = conn.cursor()

                                          try:
                                          cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
                                          rows = cur.fetchall()
                                          for row in rows:
                                          print "dropping table: ", row[1]
                                          cur.execute("drop table " + row[1] + " cascade")
                                          cur.close()
                                          conn.close()
                                          except:
                                          print "Error: ", sys.exc_info()[1]


                                          Make sure that after copying it the indentation is right since Python relies on it.






                                          share|improve this answer













                                          Just in case... Simple Python script that clean Postgresql database



                                          import psycopg2
                                          import sys

                                          # Drop all tables from a given database

                                          try:
                                          conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
                                          conn.set_isolation_level(0)
                                          except:
                                          print "Unable to connect to the database."

                                          cur = conn.cursor()

                                          try:
                                          cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
                                          rows = cur.fetchall()
                                          for row in rows:
                                          print "dropping table: ", row[1]
                                          cur.execute("drop table " + row[1] + " cascade")
                                          cur.close()
                                          conn.close()
                                          except:
                                          print "Error: ", sys.exc_info()[1]


                                          Make sure that after copying it the indentation is right since Python relies on it.







                                          share|improve this answer












                                          share|improve this answer



                                          share|improve this answer










                                          answered Oct 25 '12 at 9:31









                                          Piotr KochańskiPiotr Kochański

                                          15.2k56070




                                          15.2k56070








                                          • 1





                                            works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                            – JL Peyret
                                            Oct 20 '17 at 17:17
















                                          • 1





                                            works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                            – JL Peyret
                                            Oct 20 '17 at 17:17










                                          1




                                          1





                                          works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                          – JL Peyret
                                          Oct 20 '17 at 17:17







                                          works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.

                                          – JL Peyret
                                          Oct 20 '17 at 17:17













                                          8














                                          Use this script in pgAdmin:



                                          DO $$
                                          DECLARE
                                          brow record;
                                          BEGIN
                                          FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
                                          EXECUTE brow.table_name;
                                          END LOOP;
                                          END; $$





                                          share|improve this answer
























                                          • That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                            – Keith John Hutchison
                                            May 15 '18 at 11:15











                                          • What error did you get?

                                            – Luca Perico
                                            May 16 '18 at 13:56






                                          • 1





                                            Must have been something I did wrong, Luca. I just tried it again and it worked.

                                            – Keith John Hutchison
                                            May 17 '18 at 0:31
















                                          8














                                          Use this script in pgAdmin:



                                          DO $$
                                          DECLARE
                                          brow record;
                                          BEGIN
                                          FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
                                          EXECUTE brow.table_name;
                                          END LOOP;
                                          END; $$





                                          share|improve this answer
























                                          • That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                            – Keith John Hutchison
                                            May 15 '18 at 11:15











                                          • What error did you get?

                                            – Luca Perico
                                            May 16 '18 at 13:56






                                          • 1





                                            Must have been something I did wrong, Luca. I just tried it again and it worked.

                                            – Keith John Hutchison
                                            May 17 '18 at 0:31














                                          8












                                          8








                                          8







                                          Use this script in pgAdmin:



                                          DO $$
                                          DECLARE
                                          brow record;
                                          BEGIN
                                          FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
                                          EXECUTE brow.table_name;
                                          END LOOP;
                                          END; $$





                                          share|improve this answer













                                          Use this script in pgAdmin:



                                          DO $$
                                          DECLARE
                                          brow record;
                                          BEGIN
                                          FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
                                          EXECUTE brow.table_name;
                                          END LOOP;
                                          END; $$






                                          share|improve this answer












                                          share|improve this answer



                                          share|improve this answer










                                          answered Mar 2 '18 at 17:16









                                          Luca PericoLuca Perico

                                          460412




                                          460412













                                          • That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                            – Keith John Hutchison
                                            May 15 '18 at 11:15











                                          • What error did you get?

                                            – Luca Perico
                                            May 16 '18 at 13:56






                                          • 1





                                            Must have been something I did wrong, Luca. I just tried it again and it worked.

                                            – Keith John Hutchison
                                            May 17 '18 at 0:31



















                                          • That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                            – Keith John Hutchison
                                            May 15 '18 at 11:15











                                          • What error did you get?

                                            – Luca Perico
                                            May 16 '18 at 13:56






                                          • 1





                                            Must have been something I did wrong, Luca. I just tried it again and it worked.

                                            – Keith John Hutchison
                                            May 17 '18 at 0:31

















                                          That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                          – Keith John Hutchison
                                          May 15 '18 at 11:15





                                          That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'

                                          – Keith John Hutchison
                                          May 15 '18 at 11:15













                                          What error did you get?

                                          – Luca Perico
                                          May 16 '18 at 13:56





                                          What error did you get?

                                          – Luca Perico
                                          May 16 '18 at 13:56




                                          1




                                          1





                                          Must have been something I did wrong, Luca. I just tried it again and it worked.

                                          – Keith John Hutchison
                                          May 17 '18 at 0:31





                                          Must have been something I did wrong, Luca. I just tried it again and it worked.

                                          – Keith John Hutchison
                                          May 17 '18 at 0:31











                                          6














                                          You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE.
                                          From a bash script:



                                          #!/bin/bash
                                          TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

                                          echo Dropping tables:${TABLES}
                                          psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"





                                          share|improve this answer
























                                          • should should be #!/bin/sh

                                            – Good Person
                                            May 11 '14 at 8:04
















                                          6














                                          You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE.
                                          From a bash script:



                                          #!/bin/bash
                                          TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

                                          echo Dropping tables:${TABLES}
                                          psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"





                                          share|improve this answer
























                                          • should should be #!/bin/sh

                                            – Good Person
                                            May 11 '14 at 8:04














                                          6












                                          6








                                          6







                                          You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE.
                                          From a bash script:



                                          #!/bin/bash
                                          TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

                                          echo Dropping tables:${TABLES}
                                          psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"





                                          share|improve this answer













                                          You can use the string_agg function to make a comma-separated list, perfect for DROP TABLE.
                                          From a bash script:



                                          #!/bin/bash
                                          TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

                                          echo Dropping tables:${TABLES}
                                          psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"






                                          share|improve this answer












                                          share|improve this answer



                                          share|improve this answer










                                          answered Dec 18 '12 at 21:44









                                          JamieJamie

                                          1,3901316




                                          1,3901316













                                          • should should be #!/bin/sh

                                            – Good Person
                                            May 11 '14 at 8:04



















                                          • should should be #!/bin/sh

                                            – Good Person
                                            May 11 '14 at 8:04

















                                          should should be #!/bin/sh

                                          – Good Person
                                          May 11 '14 at 8:04





                                          should should be #!/bin/sh

                                          – Good Person
                                          May 11 '14 at 8:04











                                          4














                                          You need to drop tables and sequences, here is what worked for me



                                          psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
                                          psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX


                                          before you run the command you might need to sudo/su to the postgres user or (export connection details PGHOST, PGPORT, PGUSER and PGPASSWORD) and then export PGDATABASE=yourdatabase






                                          share|improve this answer






























                                            4














                                            You need to drop tables and sequences, here is what worked for me



                                            psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
                                            psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX


                                            before you run the command you might need to sudo/su to the postgres user or (export connection details PGHOST, PGPORT, PGUSER and PGPASSWORD) and then export PGDATABASE=yourdatabase






                                            share|improve this answer




























                                              4












                                              4








                                              4







                                              You need to drop tables and sequences, here is what worked for me



                                              psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
                                              psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX


                                              before you run the command you might need to sudo/su to the postgres user or (export connection details PGHOST, PGPORT, PGUSER and PGPASSWORD) and then export PGDATABASE=yourdatabase






                                              share|improve this answer















                                              You need to drop tables and sequences, here is what worked for me



                                              psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
                                              psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX


                                              before you run the command you might need to sudo/su to the postgres user or (export connection details PGHOST, PGPORT, PGUSER and PGPASSWORD) and then export PGDATABASE=yourdatabase







                                              share|improve this answer














                                              share|improve this answer



                                              share|improve this answer








                                              edited Aug 12 '15 at 23:28

























                                              answered Jun 7 '15 at 12:48









                                              Muayyad AlsadiMuayyad Alsadi

                                              1,036819




                                              1,036819























                                                  3














                                                  Rake task for Rails for destroy all tables in current database



                                                  namespace :db do
                                                  # rake db:drop_all_tables
                                                  task drop_all_tables: :environment do
                                                  query = <<-QUERY
                                                  SELECT
                                                  table_name
                                                  FROM
                                                  information_schema.tables
                                                  WHERE
                                                  table_type = 'BASE TABLE'
                                                  AND
                                                  table_schema NOT IN ('pg_catalog', 'information_schema');
                                                  QUERY

                                                  connection = ActiveRecord::Base.connection
                                                  results = connection.execute query

                                                  tables = results.map do |line|
                                                  table_name = line['table_name']
                                                  end.join ", "

                                                  connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
                                                  end
                                                  end





                                                  share|improve this answer



















                                                  • 1





                                                    It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                    – Steve
                                                    Sep 30 '14 at 2:15











                                                  • For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                    – Washington Botelho
                                                    Feb 4 '16 at 14:00
















                                                  3














                                                  Rake task for Rails for destroy all tables in current database



                                                  namespace :db do
                                                  # rake db:drop_all_tables
                                                  task drop_all_tables: :environment do
                                                  query = <<-QUERY
                                                  SELECT
                                                  table_name
                                                  FROM
                                                  information_schema.tables
                                                  WHERE
                                                  table_type = 'BASE TABLE'
                                                  AND
                                                  table_schema NOT IN ('pg_catalog', 'information_schema');
                                                  QUERY

                                                  connection = ActiveRecord::Base.connection
                                                  results = connection.execute query

                                                  tables = results.map do |line|
                                                  table_name = line['table_name']
                                                  end.join ", "

                                                  connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
                                                  end
                                                  end





                                                  share|improve this answer



















                                                  • 1





                                                    It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                    – Steve
                                                    Sep 30 '14 at 2:15











                                                  • For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                    – Washington Botelho
                                                    Feb 4 '16 at 14:00














                                                  3












                                                  3








                                                  3







                                                  Rake task for Rails for destroy all tables in current database



                                                  namespace :db do
                                                  # rake db:drop_all_tables
                                                  task drop_all_tables: :environment do
                                                  query = <<-QUERY
                                                  SELECT
                                                  table_name
                                                  FROM
                                                  information_schema.tables
                                                  WHERE
                                                  table_type = 'BASE TABLE'
                                                  AND
                                                  table_schema NOT IN ('pg_catalog', 'information_schema');
                                                  QUERY

                                                  connection = ActiveRecord::Base.connection
                                                  results = connection.execute query

                                                  tables = results.map do |line|
                                                  table_name = line['table_name']
                                                  end.join ", "

                                                  connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
                                                  end
                                                  end





                                                  share|improve this answer













                                                  Rake task for Rails for destroy all tables in current database



                                                  namespace :db do
                                                  # rake db:drop_all_tables
                                                  task drop_all_tables: :environment do
                                                  query = <<-QUERY
                                                  SELECT
                                                  table_name
                                                  FROM
                                                  information_schema.tables
                                                  WHERE
                                                  table_type = 'BASE TABLE'
                                                  AND
                                                  table_schema NOT IN ('pg_catalog', 'information_schema');
                                                  QUERY

                                                  connection = ActiveRecord::Base.connection
                                                  results = connection.execute query

                                                  tables = results.map do |line|
                                                  table_name = line['table_name']
                                                  end.join ", "

                                                  connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
                                                  end
                                                  end






                                                  share|improve this answer












                                                  share|improve this answer



                                                  share|improve this answer










                                                  answered Apr 21 '14 at 8:15









                                                  the-teacherthe-teacher

                                                  311317




                                                  311317








                                                  • 1





                                                    It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                    – Steve
                                                    Sep 30 '14 at 2:15











                                                  • For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                    – Washington Botelho
                                                    Feb 4 '16 at 14:00














                                                  • 1





                                                    It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                    – Steve
                                                    Sep 30 '14 at 2:15











                                                  • For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                    – Washington Botelho
                                                    Feb 4 '16 at 14:00








                                                  1




                                                  1





                                                  It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                  – Steve
                                                  Sep 30 '14 at 2:15





                                                  It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.

                                                  – Steve
                                                  Sep 30 '14 at 2:15













                                                  For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                  – Washington Botelho
                                                  Feb 4 '16 at 14:00





                                                  For some reason my schema was created with populated data. This rake works. So after do rake db:create, I run it. You can do the Steve tip and remove the code table_name = and change ", " for "," and #{ tables } fo #{tables}

                                                  – Washington Botelho
                                                  Feb 4 '16 at 14:00











                                                  3














                                                  If you want delete data (not delete table):



                                                  -- Truncate tables and restart sequnces
                                                  SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
                                                  FROM information_schema.tables
                                                  WHERE table_catalog = '<database>' AND table_schema = '<schema>';


                                                  Or if you want drop table your can use this sql:



                                                  -- For tables
                                                  SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
                                                  FROM information_schema.tables
                                                  WHERE table_catalog = '<database>' AND table_schema = '<schema>';

                                                  -- For sequences
                                                  SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
                                                  FROM information_schema.sequences
                                                  WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';





                                                  share|improve this answer




























                                                    3














                                                    If you want delete data (not delete table):



                                                    -- Truncate tables and restart sequnces
                                                    SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
                                                    FROM information_schema.tables
                                                    WHERE table_catalog = '<database>' AND table_schema = '<schema>';


                                                    Or if you want drop table your can use this sql:



                                                    -- For tables
                                                    SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
                                                    FROM information_schema.tables
                                                    WHERE table_catalog = '<database>' AND table_schema = '<schema>';

                                                    -- For sequences
                                                    SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
                                                    FROM information_schema.sequences
                                                    WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';





                                                    share|improve this answer


























                                                      3












                                                      3








                                                      3







                                                      If you want delete data (not delete table):



                                                      -- Truncate tables and restart sequnces
                                                      SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
                                                      FROM information_schema.tables
                                                      WHERE table_catalog = '<database>' AND table_schema = '<schema>';


                                                      Or if you want drop table your can use this sql:



                                                      -- For tables
                                                      SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
                                                      FROM information_schema.tables
                                                      WHERE table_catalog = '<database>' AND table_schema = '<schema>';

                                                      -- For sequences
                                                      SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
                                                      FROM information_schema.sequences
                                                      WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';





                                                      share|improve this answer













                                                      If you want delete data (not delete table):



                                                      -- Truncate tables and restart sequnces
                                                      SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
                                                      FROM information_schema.tables
                                                      WHERE table_catalog = '<database>' AND table_schema = '<schema>';


                                                      Or if you want drop table your can use this sql:



                                                      -- For tables
                                                      SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
                                                      FROM information_schema.tables
                                                      WHERE table_catalog = '<database>' AND table_schema = '<schema>';

                                                      -- For sequences
                                                      SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
                                                      FROM information_schema.sequences
                                                      WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';






                                                      share|improve this answer












                                                      share|improve this answer



                                                      share|improve this answer










                                                      answered Jan 9 '18 at 6:13









                                                      pooyapooya

                                                      9615




                                                      9615























                                                          2














                                                          I enhanced the bash method from jamie by taking care of views because his only respects the table type "base table" which is the default.



                                                          following bash code deletes the views first and then all the rest



                                                          #!/usr/bin/env bash

                                                          PGDB="yourDB"
                                                          # By exporting user & pass your dont need to interactively type them on execution
                                                          export PGUSER="PGusername"
                                                          export PGPASSWORD="PGpassword"

                                                          VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
                                                          BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

                                                          echo Dropping views:${VIEWS}
                                                          psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
                                                          echo Dropping tables:${BASETBLS}
                                                          psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"





                                                          share|improve this answer
























                                                          • Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                            – raminr
                                                            Jun 14 '15 at 17:53


















                                                          2














                                                          I enhanced the bash method from jamie by taking care of views because his only respects the table type "base table" which is the default.



                                                          following bash code deletes the views first and then all the rest



                                                          #!/usr/bin/env bash

                                                          PGDB="yourDB"
                                                          # By exporting user & pass your dont need to interactively type them on execution
                                                          export PGUSER="PGusername"
                                                          export PGPASSWORD="PGpassword"

                                                          VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
                                                          BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

                                                          echo Dropping views:${VIEWS}
                                                          psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
                                                          echo Dropping tables:${BASETBLS}
                                                          psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"





                                                          share|improve this answer
























                                                          • Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                            – raminr
                                                            Jun 14 '15 at 17:53
















                                                          2












                                                          2








                                                          2







                                                          I enhanced the bash method from jamie by taking care of views because his only respects the table type "base table" which is the default.



                                                          following bash code deletes the views first and then all the rest



                                                          #!/usr/bin/env bash

                                                          PGDB="yourDB"
                                                          # By exporting user & pass your dont need to interactively type them on execution
                                                          export PGUSER="PGusername"
                                                          export PGPASSWORD="PGpassword"

                                                          VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
                                                          BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

                                                          echo Dropping views:${VIEWS}
                                                          psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
                                                          echo Dropping tables:${BASETBLS}
                                                          psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"





                                                          share|improve this answer













                                                          I enhanced the bash method from jamie by taking care of views because his only respects the table type "base table" which is the default.



                                                          following bash code deletes the views first and then all the rest



                                                          #!/usr/bin/env bash

                                                          PGDB="yourDB"
                                                          # By exporting user & pass your dont need to interactively type them on execution
                                                          export PGUSER="PGusername"
                                                          export PGPASSWORD="PGpassword"

                                                          VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
                                                          BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

                                                          echo Dropping views:${VIEWS}
                                                          psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
                                                          echo Dropping tables:${BASETBLS}
                                                          psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"






                                                          share|improve this answer












                                                          share|improve this answer



                                                          share|improve this answer










                                                          answered Apr 24 '14 at 12:53









                                                          martinseenermartinseener

                                                          313




                                                          313













                                                          • Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                            – raminr
                                                            Jun 14 '15 at 17:53





















                                                          • Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                            – raminr
                                                            Jun 14 '15 at 17:53



















                                                          Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                          – raminr
                                                          Jun 14 '15 at 17:53







                                                          Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"

                                                          – raminr
                                                          Jun 14 '15 at 17:53













                                                          2














                                                          in a Windows batch file:



                                                          @echo off
                                                          FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="dt" YOUR_TABLE_NAME') DO (
                                                          psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
                                                          echo table %%G dropped
                                                          )





                                                          share|improve this answer






























                                                            2














                                                            in a Windows batch file:



                                                            @echo off
                                                            FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="dt" YOUR_TABLE_NAME') DO (
                                                            psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
                                                            echo table %%G dropped
                                                            )





                                                            share|improve this answer




























                                                              2












                                                              2








                                                              2







                                                              in a Windows batch file:



                                                              @echo off
                                                              FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="dt" YOUR_TABLE_NAME') DO (
                                                              psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
                                                              echo table %%G dropped
                                                              )





                                                              share|improve this answer















                                                              in a Windows batch file:



                                                              @echo off
                                                              FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="dt" YOUR_TABLE_NAME') DO (
                                                              psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
                                                              echo table %%G dropped
                                                              )






                                                              share|improve this answer














                                                              share|improve this answer



                                                              share|improve this answer








                                                              edited Dec 2 '15 at 19:47









                                                              johntellsall

                                                              8,53522626




                                                              8,53522626










                                                              answered Aug 15 '13 at 20:18









                                                              LewisLewis

                                                              211




                                                              211























                                                                  0














                                                                  well, since I like working from the command line...



                                                                  psql -U <user> -d <mydb> -c 'dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"



                                                                  -c 'dt' will invoke the list tables command.




                                                                  List of relations
                                                                  Schema | Name | Type | Owner
                                                                  --------+-------------------+-------+----------
                                                                  public | _d_psidxddlparm | table | djuser
                                                                  public | _d_psindexdefn | table | djuser



                                                                  cut -d ' ' -f 4 now, pipe its output to grab the 4th field (when using space as separator), which is the table.



                                                                  sed is then used to prefix a drop table and suffix the ; command separator.



                                                                  | egrep '_d_' - Pipe it into grep some more and you can be more selective about which tables you drop.




                                                                  drop table if exists _d_psidxddlparm;
                                                                  drop table if exists _d_psindexdefn;



                                                                  Note: as written, this will generate bogus rows for the dt commands output of column headers and total rows at the end. I avoid that by grepping, but you could use head and tail.






                                                                  share|improve this answer






























                                                                    0














                                                                    well, since I like working from the command line...



                                                                    psql -U <user> -d <mydb> -c 'dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"



                                                                    -c 'dt' will invoke the list tables command.




                                                                    List of relations
                                                                    Schema | Name | Type | Owner
                                                                    --------+-------------------+-------+----------
                                                                    public | _d_psidxddlparm | table | djuser
                                                                    public | _d_psindexdefn | table | djuser



                                                                    cut -d ' ' -f 4 now, pipe its output to grab the 4th field (when using space as separator), which is the table.



                                                                    sed is then used to prefix a drop table and suffix the ; command separator.



                                                                    | egrep '_d_' - Pipe it into grep some more and you can be more selective about which tables you drop.




                                                                    drop table if exists _d_psidxddlparm;
                                                                    drop table if exists _d_psindexdefn;



                                                                    Note: as written, this will generate bogus rows for the dt commands output of column headers and total rows at the end. I avoid that by grepping, but you could use head and tail.






                                                                    share|improve this answer




























                                                                      0












                                                                      0








                                                                      0







                                                                      well, since I like working from the command line...



                                                                      psql -U <user> -d <mydb> -c 'dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"



                                                                      -c 'dt' will invoke the list tables command.




                                                                      List of relations
                                                                      Schema | Name | Type | Owner
                                                                      --------+-------------------+-------+----------
                                                                      public | _d_psidxddlparm | table | djuser
                                                                      public | _d_psindexdefn | table | djuser



                                                                      cut -d ' ' -f 4 now, pipe its output to grab the 4th field (when using space as separator), which is the table.



                                                                      sed is then used to prefix a drop table and suffix the ; command separator.



                                                                      | egrep '_d_' - Pipe it into grep some more and you can be more selective about which tables you drop.




                                                                      drop table if exists _d_psidxddlparm;
                                                                      drop table if exists _d_psindexdefn;



                                                                      Note: as written, this will generate bogus rows for the dt commands output of column headers and total rows at the end. I avoid that by grepping, but you could use head and tail.






                                                                      share|improve this answer















                                                                      well, since I like working from the command line...



                                                                      psql -U <user> -d <mydb> -c 'dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"



                                                                      -c 'dt' will invoke the list tables command.




                                                                      List of relations
                                                                      Schema | Name | Type | Owner
                                                                      --------+-------------------+-------+----------
                                                                      public | _d_psidxddlparm | table | djuser
                                                                      public | _d_psindexdefn | table | djuser



                                                                      cut -d ' ' -f 4 now, pipe its output to grab the 4th field (when using space as separator), which is the table.



                                                                      sed is then used to prefix a drop table and suffix the ; command separator.



                                                                      | egrep '_d_' - Pipe it into grep some more and you can be more selective about which tables you drop.




                                                                      drop table if exists _d_psidxddlparm;
                                                                      drop table if exists _d_psindexdefn;



                                                                      Note: as written, this will generate bogus rows for the dt commands output of column headers and total rows at the end. I avoid that by grepping, but you could use head and tail.







                                                                      share|improve this answer














                                                                      share|improve this answer



                                                                      share|improve this answer








                                                                      edited Apr 17 '18 at 20:48

























                                                                      answered Apr 17 '18 at 20:36









                                                                      JL PeyretJL Peyret

                                                                      2,9871630




                                                                      2,9871630























                                                                          0














                                                                          The easiest way is to drop the public schema as others have suggested in previous answers. However, this is NOT a good way. You never know what has been done to the public schema that has since been forgotten and was not documented. You also don't know if this will work the same into the future. In V9, it would have been fine, but in V10 all your users would loose access to the schema, and must be granted access again otherwise your application will break. I haven't checked V11, but the point is that you never know what will break as you move from machine to machine, site to site or version to version. It also cannot be done if you are a user that has access to the database, but not to the schema.



                                                                          If you need to do this programmatically then other answers above cover this, but one thing the answers above don't consider is to get Postgres to do the work for you. If you use pg_dump with the -c option as below:



                                                                          sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""


                                                                          That will create a DB restore script with sql statements that will delete all the tables.



                                                                          If the only purpose in asking the question was to delete the tables prior to restore, then your restore will do the work for you.



                                                                          However, if you need it for something else, you can simply copy the drop statements from the sql script.






                                                                          share|improve this answer




























                                                                            0














                                                                            The easiest way is to drop the public schema as others have suggested in previous answers. However, this is NOT a good way. You never know what has been done to the public schema that has since been forgotten and was not documented. You also don't know if this will work the same into the future. In V9, it would have been fine, but in V10 all your users would loose access to the schema, and must be granted access again otherwise your application will break. I haven't checked V11, but the point is that you never know what will break as you move from machine to machine, site to site or version to version. It also cannot be done if you are a user that has access to the database, but not to the schema.



                                                                            If you need to do this programmatically then other answers above cover this, but one thing the answers above don't consider is to get Postgres to do the work for you. If you use pg_dump with the -c option as below:



                                                                            sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""


                                                                            That will create a DB restore script with sql statements that will delete all the tables.



                                                                            If the only purpose in asking the question was to delete the tables prior to restore, then your restore will do the work for you.



                                                                            However, if you need it for something else, you can simply copy the drop statements from the sql script.






                                                                            share|improve this answer


























                                                                              0












                                                                              0








                                                                              0







                                                                              The easiest way is to drop the public schema as others have suggested in previous answers. However, this is NOT a good way. You never know what has been done to the public schema that has since been forgotten and was not documented. You also don't know if this will work the same into the future. In V9, it would have been fine, but in V10 all your users would loose access to the schema, and must be granted access again otherwise your application will break. I haven't checked V11, but the point is that you never know what will break as you move from machine to machine, site to site or version to version. It also cannot be done if you are a user that has access to the database, but not to the schema.



                                                                              If you need to do this programmatically then other answers above cover this, but one thing the answers above don't consider is to get Postgres to do the work for you. If you use pg_dump with the -c option as below:



                                                                              sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""


                                                                              That will create a DB restore script with sql statements that will delete all the tables.



                                                                              If the only purpose in asking the question was to delete the tables prior to restore, then your restore will do the work for you.



                                                                              However, if you need it for something else, you can simply copy the drop statements from the sql script.






                                                                              share|improve this answer













                                                                              The easiest way is to drop the public schema as others have suggested in previous answers. However, this is NOT a good way. You never know what has been done to the public schema that has since been forgotten and was not documented. You also don't know if this will work the same into the future. In V9, it would have been fine, but in V10 all your users would loose access to the schema, and must be granted access again otherwise your application will break. I haven't checked V11, but the point is that you never know what will break as you move from machine to machine, site to site or version to version. It also cannot be done if you are a user that has access to the database, but not to the schema.



                                                                              If you need to do this programmatically then other answers above cover this, but one thing the answers above don't consider is to get Postgres to do the work for you. If you use pg_dump with the -c option as below:



                                                                              sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""


                                                                              That will create a DB restore script with sql statements that will delete all the tables.



                                                                              If the only purpose in asking the question was to delete the tables prior to restore, then your restore will do the work for you.



                                                                              However, if you need it for something else, you can simply copy the drop statements from the sql script.







                                                                              share|improve this answer












                                                                              share|improve this answer



                                                                              share|improve this answer










                                                                              answered Dec 28 '18 at 21:29









                                                                              RichardPRichardP

                                                                              47257




                                                                              47257






























                                                                                  draft saved

                                                                                  draft discarded




















































                                                                                  Thanks for contributing an answer to Stack Overflow!


                                                                                  • Please be sure to answer the question. Provide details and share your research!

                                                                                  But avoid



                                                                                  • Asking for help, clarification, or responding to other answers.

                                                                                  • Making statements based on opinion; back them up with references or personal experience.


                                                                                  To learn more, see our tips on writing great answers.




                                                                                  draft saved


                                                                                  draft discarded














                                                                                  StackExchange.ready(
                                                                                  function () {
                                                                                  StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3327312%2fdrop-all-tables-in-postgresql%23new-answer', 'question_page');
                                                                                  }
                                                                                  );

                                                                                  Post as a guest















                                                                                  Required, but never shown





















































                                                                                  Required, but never shown














                                                                                  Required, but never shown












                                                                                  Required, but never shown







                                                                                  Required, but never shown

































                                                                                  Required, but never shown














                                                                                  Required, but never shown












                                                                                  Required, but never shown







                                                                                  Required, but never shown







                                                                                  ZeoJFzKh90kZ0Hg0i
                                                                                  f8qGkGSXow6 HDj D r6FyyGrOLkIA

                                                                                  Popular posts from this blog

                                                                                  Monofisismo

                                                                                  Angular Downloading a file using contenturl with Basic Authentication

                                                                                  Olmecas