How can I drop all the tables in a PostgreSQL database

Question

How can I drop 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

User · Answer

well  since I like working from the command line     psql -U  lt user gt  -d  lt mydb gt  -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

User · 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    lt class  psycopg2 ProgrammingError  gt  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 in pgAdmin III  I used the Query tool   magnifying glass icon  Execute abritrary SQL queries   or you could use Plugins-  PSQL Console  Note  If you have any extensions installed they will be dropped when you drop the schema  so you should make note of what you need installed and then execute statements as necessary  E g   CREATE EXTENSION postgis

User · Answer

Here s the ready-made query for you  SELECT  drop table if exists  quot      tablename      quot  cascade   as pg drop  FROM pg tables  WHERE schemaname  your schema

User · 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

User · 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

User · Answer

Following steps might be helpful  For linux users     At first enter the postgres command prompt by following command   sudo -u postgres psql  Enter the database by this command  my database name is  maoss     c maoss  Now enter the command for droping all tables   DROP SCHEMA public CASCADE  CREATE SCHEMA public   GRANT ALL ON SCHEMA public TO postgres  GRANT ALL ON SCHEMA public TO public

User · 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    lt  lt -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

User · 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

User · 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

User · Answer

Note  my answer is about really deleting the tables and other database objects  for deleting all data in the tables  i e  truncating all tables  Endre Both has provided a similarily well-executed  direct execution  statement a month later   For the cases where you can   t just DROP SCHEMA public CASCADE   DROP OWNED BY current user  or something  here   s a stand-alone SQL script I wrote  which is transaction-safe  i e  you can put it between BEGIN  and either ROLLBACK  to just test it out or COMMIT  to actually do the deed  and cleans up    all    database objects    well  all those used in the database our application uses or I could sensibly add  which is    triggers on tables constraints on tables  FK  PK  CHECK  UNIQUE  indices VIEWs  normal or materialised  tables sequences routines  aggregate functions  functions  procedures  all non-default  i e  not public or DB-internal  schemata    we    own  the script is useful when run as    not a database superuser     a superuser can drop all schemata  the really important ones are still explicitly excluded  though  extensions  user-contributed but I normally deliberately leave them in    Not dropped are  some deliberate  some only because I had no example in our DB     the public schema  e g  for extension-provided stuff in them  collations and other locale stuff event triggers text search stuff       see here for other stuff I might have missed  roles or other security settings composite types toast tables FDW and foreign tables   This is really useful for the cases when the dump you want to restore is of a different database schema version  e g  with Debian dbconfig-common  Flyway or Liquibase DB-Manul  than the database you want to restore it into   I   ve also got a version which deletes    everything except two tables and what belongs to them     a sequence  tested manually  sorry  I know  boring  in case someone is interested  the diff is small  Contact me or check this repo if interested   SQL  -- Copyright    2019  2020 --      mirabilos  lt t glaser tarent de gt  -- -- Provided that these terms and disclaimer and all copyright notices -- are retained or reproduced in an accompanying document  permission -- is granted to deal in this work without restriction  including un- -- limited rights to use  publicly perform  distribute  sell  modify  -- merge  give away  or sublicence  -- -- This work is provided    AS IS    and WITHOUT WARRANTY of any kind  to -- the utmost extent permitted by applicable law  neither express nor -- implied  without malicious intent or gross negligence  In no event -- may a licensor  author or contributor be held liable for indirect  -- direct  other damage  loss  or other issues arising in any way out -- of dealing in the work  even if advised of the possibility of such -- damage or existence of a defect  except proven that it results out -- of said person   s immediate fault when using the work as intended  -- - -- Drop everything from the PostgreSQL database   DO    DECLARE         q TEXT          r RECORD  BEGIN         -- triggers         FOR r IN  SELECT pns nspname  pc relname  pt tgname                 FROM pg catalog pg trigger pt  pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace AND pc oid pt tgrelid                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pt tgisinternal false               LOOP                 EXECUTE format  DROP TRIGGER  I ON  I  I                        r tgname  r nspname  r relname           END LOOP          -- constraints  1  foreign key         FOR r IN  SELECT pns nspname  pc relname  pcon conname                 FROM pg catalog pg constraint pcon  pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace AND pc oid pcon conrelid                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pcon contype  f                LOOP                 EXECUTE format  ALTER TABLE ONLY  I  I DROP CONSTRAINT  I                        r nspname  r relname  r conname           END LOOP          -- constraints  2  the rest         FOR r IN  SELECT pns nspname  pc relname  pcon conname                 FROM pg catalog pg constraint pcon  pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace AND pc oid pcon conrelid                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pcon contype lt  gt  f                LOOP                 EXECUTE format  ALTER TABLE ONLY  I  I DROP CONSTRAINT  I                        r nspname  r relname  r conname           END LOOP          -- indices         FOR r IN  SELECT pns nspname  pc relname                 FROM pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pc relkind  i                LOOP                 EXECUTE format  DROP INDEX  I  I                        r nspname  r relname           END LOOP          -- normal and materialised views         FOR r IN  SELECT pns nspname  pc relname                 FROM pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pc relkind IN   v    m                 LOOP                 EXECUTE format  DROP VIEW  I  I                        r nspname  r relname           END LOOP          -- tables         FOR r IN  SELECT pns nspname  pc relname                 FROM pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pc relkind  r                LOOP                 EXECUTE format  DROP TABLE  I  I                        r nspname  r relname           END LOOP          -- sequences         FOR r IN  SELECT pns nspname  pc relname                 FROM pg catalog pg class pc  pg catalog pg namespace pns                 WHERE pns oid pc relnamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pc relkind  S                LOOP                 EXECUTE format  DROP SEQUENCE  I  I                        r nspname  r relname           END LOOP          -- extensions  only if necessary  keep them normally          FOR r IN  SELECT pns nspname  pe extname                 FROM pg catalog pg extension pe  pg catalog pg namespace pns                 WHERE pns oid pe extnamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                 LOOP                 EXECUTE format  DROP EXTENSION  I    r extname           END LOOP          -- aggregate functions first  because they depend on other functions          FOR r IN  SELECT pns nspname  pp proname  pp oid                 FROM pg catalog pg proc pp  pg catalog pg namespace pns  pg catalog pg aggregate pagg                 WHERE pns oid pp pronamespace                     AND pns nspname NOT IN   information schema    pg catalog    pg toast                       AND pagg aggfnoid pp oid               LOOP                 EXECUTE format  DROP AGGREGATE  I  I  s                         r nspname  r proname                      pg get function identity arguments r oid            END LOOP          -- routines  functions  aggregate functions  procedures  window functions          IF EXISTS  SELECT   FROM pg catalog pg attribute                 WHERE attrelid  pg catalog pg proc   regclass                     AND attname  prokind  -- PostgreSQL 11                THEN                 q     CASE pp prokind                         WHEN   p   THEN   PROCEDURE                           WHEN   a   THEN   AGGREGATE                           ELSE   FUNCTION                       END           ELSIF EXISTS  SELECT   FROM pg catalog pg attribute                 WHERE attrelid  pg catalog pg proc   regclass                     AND attname  proisagg  -- PostgreSQL  10               THEN                 q     CASE pp proisagg                         WHEN true THEN   AGGREGATE                           ELSE   FUNCTION                       END           ELSE                 q       FUNCTION             END IF          FOR r IN EXECUTE  SELECT pns nspname  pp proname  pp oid       q      AS pt                 FROM pg catalog pg proc pp  pg catalog pg namespace pns                 WHERE pns oid pp pronamespace                     AND pns nspname NOT IN    information schema      pg catalog      pg toast                  LOOP                 EXECUTE format  DROP  s  I  I  s     r pt                      r nspname  r proname                      pg get function identity arguments r oid            END LOOP          -- non-default schemata we own  assume to be run by a not-superuser         FOR r IN  SELECT pns nspname                 FROM pg catalog pg namespace pns  pg catalog pg roles pr                 WHERE pr oid pns nspowner                     AND pns nspname NOT IN   information schema    pg catalog    pg toast    public                       AND pr rolname current user               LOOP                 EXECUTE format  DROP SCHEMA  I    r nspname           END LOOP          -- voil           RAISE NOTICE  Database cleared    END        Tested  except later additions  extensions contributed by Cl  ment Pr  vost   on PostgreSQL 9 6  jessie-backports   Aggregate removal tested on 9 6 and 12 2  procedure removal tested on 12 2 as well  Bugfixes and further improvements welcome

User · 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

User · Answer

If you want to nuke all tables anyway  you can dispense with niceties such as CASCADE by putting all tables into a single statement  This also makes execution quicker   SELECT  TRUNCATE TABLE      string agg        tablename                       FROM pg tables WHERE schemaname    public     Executing it directly   DO    DECLARE tablenames text  BEGIN         tablenames    string agg        tablename                        FROM pg tables WHERE schemaname    public       EXECUTE  TRUNCATE TABLE      tablenames  END       Replace TRUNCATE with DROP as applicable

User · 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

User · Answer

This is a really interesting question  and you ll get it done in Multiple ways  1  By dropping and recreating the current schema Here  In general  we have a public schema by default  So  I m using it as an instance  DROP SCHEMA  public  CASCADE  CREATE SCHEMA  public    GRANT ALL ON SCHEMA public TO postgres  GRANT ALL ON SCHEMA public TO public   If you are using PostgreSQL 9 3 or greater  you may also need to restore the default grants  Pros  This will clean an entire Schema and re-create it as a new one  Cons  You ll lose other entities too like Functions  Views  Materialized views  etc  2  By using fetching all table names from pg tables table  PostgreSQL stores all the tables on its record table named pg table  SELECT    DROP TABLE IF EXISTS  quot      tablename      quot  CASCADE    from   pg tables WHERE schemaname    public    As you can see  By the use of subquery  We can remove the entire tables from the schema  Pros  When the other data entities are Important and you just want to delete only tables from the schema  this approach will really helpful to you  3  Terminal  Login using postgres user on your shell    sudo -u postgres psql   Connect your database     c mydatabase  Paste these commands  DROP SCHEMA public CASCADE  CREATE SCHEMA public        GRANT ALL ON SCHEMA public TO postgres  GRANT ALL ON SCHEMA public TO public   Note  This bunch of commands are similar to the first point  so the Pros and cons will remain the same

User · 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     lt database gt   AND table schema     lt schema gt      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     lt database gt   AND table schema     lt schema gt     -- For sequences SELECT  DROP SEQUENCE d a seq       sequence schema             sequence name          FROM information schema sequences  WHERE sequence catalog     lt database gt   AND sequence schema     lt schema gt

User · 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  amp  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

User · 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

User · 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

User · 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

User · 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

User · Answer

drop schema public cascade    should do the trick

User · 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

User · 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

User · 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

User · 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

[postgresql] How can I drop all the tables in a PostgreSQL database?

Examples related to postgresql