Home Drop all tables in PostgreSQL?
Reply: 18

Drop all tables in PostgreSQL?

AP257
1#
AP257 Published in 2010-07-24 23:24:05Z

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.

Pablo Santa Cruz
2#
Pablo Santa Cruz Reply to 2012-10-29 17:03:22Z

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.

MAbraham1
3#
MAbraham1 Reply to 2012-06-04 20:15:55Z

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';
Mark Lawrence
4#
Mark Lawrence Reply to 2012-07-16 03:13:47Z

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.

Tim Diggins
5#
Tim Diggins Reply to 2012-10-23 15:03:30Z

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

Piotr Kochański
6#
Piotr Kochański Reply to 2012-10-25 09:31:00Z

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.

Joe Van Dyk
7#
Joe Van Dyk Reply to 2012-10-28 01:17:01Z
drop schema public cascade;

should do the trick.

biniam_Ethiopia
8#
biniam_Ethiopia Reply to 2016-10-19 09:45:03Z

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;
Jamie
9#
Jamie Reply to 2012-12-18 21:44:30Z

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"
johntellsall
10#
johntellsall Reply to 2015-12-02 19:47:47Z

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
11#
User Reply to 2014-01-21 00:38:21Z

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)

the-teacher
12#
the-teacher Reply to 2014-04-21 08:15:40Z

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
martinseener
13#
martinseener Reply to 2014-04-24 12:53:02Z

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"
muayyad alsadi
14#
muayyad alsadi Reply to 2015-08-12 23:28:11Z

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

a_horse_with_no_name
15#
a_horse_with_no_name Reply to 2015-12-01 07:17:23Z

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

drop owned by the_user;

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.

This will also drop views, sequences, triggers and so on that the_user owns (=created).

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

Piotr Findeisen
16#
Piotr Findeisen Reply to 2016-08-09 09:43:04Z

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.

Adé
17#
Adé Reply to 2017-07-07 23:38:01Z

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'
Viorel
18#
Viorel Reply to 2017-12-27 09:57:55Z

Don't know why people complicate themselves, in CMD, it would be as simple as:

psql.exe -d [dbname] --username "postgres" -c "DROP SCHEMA IF EXISTS [schema_name] CASCADE;"

And try not to use public as your work schema.

pooya
19#
pooya Reply to 2018-01-09 06:13:33Z

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>';
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.372128 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO