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.

Morehead

Answer 1

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 later, you may also need to restore the default grants.

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

Snaky

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

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. – Sawdust

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) – Dore

@mgojohn: If you have DROP SCHEMA privileges, you are probably postgres anyway. – Ita

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

@Psaltery Why would you want GRANT ALL after the create? – Chondroma

@Federico, sure, but only assuming the public schema had default grants. See my answer for code that definitely drops all and only tables and not much longer than this one. – Tetrafluoroethylene

GRANT part is really important, I think answer should be updated to reflect that. After I used this answer my Java application that used Hibernate and logged to DB using custom logon could not create sequence. This is pretty strange because app was able to create tables, only sequence could not be created. – Glider

adding this works like a charm : alter schema public owner to user; – Lennielenno

It doesn't work for me. When I dropped schema public, I tried to load backup from sql file and saw errors that some constraints are violated! – Nummulite

This will break TimescaleDB if you are using that. Don't ask me how I know. – Mitchellmitchem

Note! That this will also delete all indexes, constraints etc. – Darladarlan

@Nummulite Same here. Something persists. When I restore db I get error messages "duplicate key value violates unique constraint" on some BLOB restore. There is no such problem on clean db. pg_restore --clean solved this for me. – Lynseylynus

Answer 2

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.

Kerosene

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

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

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 – Meerschaum

Why is this preferable to @Derek Slife's two line answer? – Packer

@Packer because pg_tables has a bunch of other goodies so you can do things like only drop tables you own, for example. – Sawdust

@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. – Windy

@Packer also; dropping a schema drops all object types (functions, sequences, etc) as well as permissions granted on the schema itself. When recreating the schema, you would have to reapply the permissions and add any desired functions that existed on/in the schema prior to dropping. So it could be more maintenance depending on what's required. – Premillenarian

Version for not public schemas: select 'drop table if exists "' || schemaname || '"."' || tablename || '" cascade;' from pg_tables where schemaname = 'user_data'; – Lympho

You can write a shell script to run this query, then pipe the output into a new psql session. I did something similar a few years ago. – Secondary

\gexec will automatically run the commands. – Spat

To turn off psql's pager: \pset pager 0 – Magree

can anybody explain why we need to double quote the tablename? For me, it gets more visual clutter. Plus it adds unnecessary quote when you tried to dump the output to a file using \copy – Nonsuit

You need the " in case you have a table that contains spaces on its name. – Kerosene

Answer 3

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 original 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 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;

Abjuration

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. – Hermosa

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

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; – Masqat

Only to add conciseness, both GRANTs can be done in one line: GRANT ALL ON SCHEMA public TO postgres, public; – Subversion

Answer 4

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.

Tetrafluoroethylene

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 :) – Agni

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... – Langevin

I would suggest to change that line EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE'; with this: EXECUTE format('DROP TABLE IF EXISTS %I CASCADE', quote_ident(r.tablename)); – Semple

@Semple Why? It looks like an unnecessary complication to me. Is there an injection possibility (and does this really fix one if there is)? [I don't know if Postgres is foolish enough to allow table names to make that possible] If there is, you should really change your comment to an edit in the answer (explaining why in the edit comments). – Discriminator

EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE;'; this works better for foreign keys – Oregon

For anyone who doesn't understand, just query from the database you want to drop all the tables, and execute the answer's query, nothing need to be changed. Done! – Unleash

@Semple using quote_ident is redundant when you're formatting it with %I – Rance

Answer 5

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

Anderlecht

I would use drop owned by current_user; This way you don't even have to worry about typing the correct username. – Flavourful

Actually a very good solution for me. My database and public schema are owned by postgres, but everything else is owned by a specific user, so dropping everything owned by that user clears the database except for the schema. – Discriminator

Note that the documentation says it will revoke privileges but if you run this as a normal user it can't, so it just drops the tables and the like, which is exactly what I want. Nice! – Ardeliaardelis

Answer 6

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.

-- Recreate the schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Restore default permissions
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 "' || tablename || '" 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 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 is similar to the first point, so the Pros and cons will remain the same..

Plotkin

Answer 7

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';

Faithfaithful

I used this, which worked for me. I assume the where schemaname='public' part is significant? – Martita

@Martita If you leave out that you may potentially try deleting all internal postgres tables as well, which is most likely not what you want. – Scripture

A safer option would be: select 'drop table "' || tablename || '" cascade;' from pg_tables where tableowner = 'some_user'; – Coxa

Answer 8

drop schema public cascade;

should do the trick.

Oneeyed

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

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 – Jonathanjonathon

Answer 9

Following steps might be helpful (For linux users):

1. At first enter the postgres command prompt by following command:

   sudo -u postgres psql
   

2. Enter the database by this command (my database name is: maoss):

   \c maoss
   

3. 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;
   

4. Now exit from psql by following command:

   \q
   

Libya

Answer 10

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

Mesitylene

Answer 11

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.

Selfabsorption

Answer 12

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.

Trottier

when not operating on the public schema, do not forget to include the schema name in the expression: string_agg(quote_ident(schemaname) || '.' || quote_ident(tablename), ', ') instead of merely passing the table names. – Koger

Answer 13

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'

Washedout

Answer 14

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.

Layout

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. – Hellhole

conn.set_isolation_level(0) is very important otherwise the drop query just gets stuck – Bestead

Answer 15

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; $$

Octameter

Answer 16

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>';

Condign

Answer 17

Just execute the query bellow:

DO $$ DECLARE
    r RECORD;
BEGIN
    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 $$;

Mercurous

Answer 18

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"

Raceway

should should be #!/bin/sh – Blither

Answer 19

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)
• indicēs
• VIEWs (normal or materialised)
• tables
• sequences
• routines (aggregate functions, functions, procedures)
• all nōn-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 <[email protected]>
--
-- 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<>'f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- indicēs
        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;
        -- nōn-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!

Jigging

There's errors in the above script as it does not distinguish between functions and procedures: DROP FUNCTION fails for a procedure, and vice versa. I modified the function section to this: AND pp.prokind ='f' -- Function or AND pp.prokind ='p' -- Procedure – Pursuant

@Pursuant That’s not an error, the omission of aggregate functions was documented and the script was documented to be tested on 9.6 only. But I took your comment to heart and adapted it to handle aggregates (proisagg) on ≤ 10.x and aggregates and procedures (prokind) on ≥ 11 (checked dynamically) and tested both ☻ thanks for the hint. – Jigging

Answer 20

Just run this from your query tool in pgAdmin

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

and you are all set.

Kropp

Answer 21

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

Burgett

Answer 22

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
)

Lordsandladies

Answer 23

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

Sylvestersylvia

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

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} – Scarletscarlett

Answer 24

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"

Composed

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'" – Disdainful

Answer 25

Using PSQL with \gexec

This is a far more comprehensive query then the ones thus far, as it will work with special table names.

SELECT FORMAT('DROP TABLE %I.%I.%I CASCADE;', table_catalog, table_schema, table_name)
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema <> 'information_schema'
  AND table_schema NOT LIKE 'pg_%';

You can preview the commands to be run, and you can execute the output of this query by typing \gexec after you run it in psql.

NOTE: The use of CASCADE will drop all things (like VIEWs) that depend on the table

Contango

Answer 26

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.

Hellhole

Answer 27

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.

Simpatico

Answer 28

For those using postgis and wanting to delete from public:-

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename != 'spatial_ref_sys') LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

Hammerskjold

Answer 29

Please make sure you have a backup of your database before attempting this. To delete all tables in a PostgreSQL database, you can use the following steps:

Use a PostgreSQL client like table plus, pgAdmin or command line tool to connect to your database.

Run the following SQL command:

This command will generate a series of DROP TABLE statements for all tables in the current schema and execute them.

DO $$ 
DECLARE 
   tabname RECORD; 
BEGIN 
   FOR tabname IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') 
   LOOP 
      EXECUTE 'DROP TABLE IF EXISTS ' || tabname.tablename || ' CASCADE'; 
   END LOOP; 
END $$;

This query retrieves all table names in the 'public' schema and drops them one by one using dynamic SQL.

The CASCADE keyword ensures that dependent objects like views or foreign keys are also dropped.

Note: This is an irreversible sql query, so be sure of what you're doing.

Hebrides

Answer 30

A terminal based approach using psql worked best for me. I even created a bash function because it is convenient for development:

psqlDropTables() {
    PGPASSWORD=<your password>
    PGTABLE=<your table name>
    PGUSER=<your pg user name>
    PGPASSWORD=$PGPASSWORD psql -ah 127.0.0.1 $PGTABLE $PGUSER -c "
      SELECT
'DROP TABLE IF EXISTS \"' || tablename || '\" CASCADE;' from
pg_tables WHERE schemaname = 'public';" | grep DROP | awk 'NR>1{print $0}' | sed "s/\"/'/g" | PGPASSWORD=$PGPASSWORD xargs -i  psql -ah 127.0.0.1 $PGTABLE $PGUSER -c {}
}

It creates all the required drop table statements as stated in this response, replaces the " with ' and runs them on the DB.

Vole

Answer 31

If you are going to restore dump on this db, then

pg_restore --clean

can do it for you.

--clean Before restoring database objects, issue commands to DROP all the objects that will be restored. This option is useful for overwriting an existing database. If any of the objects do not exist in the destination database, ignorable error messages will be reported, unless --if-exists is also specified.

Lynseylynus

Answer 32

Maybe the simplest way is:

1. Drop database contains that tables with:

   drop database DATABASE_NAME;

2. Recreate that database:

   create database DATABASE_NAME;

Cautery

Does not work, when your user does not have right to create database. IMHO this is original idea, why dropping all tables/sequences is needed. – Kickapoo

Answer 33

Here's the ready-made query for you:

SELECT

'drop table if exists "' || tablename || '" cascade;' as pg_drop

FROM

pg_tables

WHERE

schemaname='your schema';

Pollypollyanna

Answer 34

for macOS. If you have a terminal via PostgreSQL application, a simple command can help you:

drop table "organisations" cascade;

Tyika