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.
This question is related to
postgresql
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
)
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'
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';
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;
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"
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.
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
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
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:
CHECK, UNIQUE)VIEWs (normal or materialised)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)Not dropped are (some deliberate; some only because I had no example in our DB):
public schema (e.g. for extension-provided stuff in them)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.
-- 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;
-- 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!
drop schema public cascade;
should do the trick.
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';
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
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.
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.
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>';
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.
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.
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 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;
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
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"
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.
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; $$
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;
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.
This is a really interesting question, and you'll get it done in Multiple ways:
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.
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 just want to delete only tables from the schema, this approach will really helpful to you.
$ sudo -u postgres psql
$ \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.
Source: Stackoverflow.com