Drop all tables in PostgreSQL?

Multi tool use
How can I delete all tables in PostgreSQL, working from the command line?
I don't want to drop the database itself, just all tables and all the data in them.
postgresql
|
show 4 more comments
How can I delete all tables in PostgreSQL, working from the command line?
I don't want to drop the database itself, just all tables and all the data in them.
postgresql
2
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
2
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
82
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
12
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
2
BTW, when you droppublic
, you lose any installed extensions.
– sudo
Jul 8 '16 at 16:53
|
show 4 more comments
How can I delete all tables in PostgreSQL, working from the command line?
I don't want to drop the database itself, just all tables and all the data in them.
postgresql
How can I delete all tables in PostgreSQL, working from the command line?
I don't want to drop the database itself, just all tables and all the data in them.
postgresql
postgresql
edited Oct 12 '16 at 15:32


Oldskool
27.4k74563
27.4k74563
asked Jul 24 '10 at 23:24
AP257AP257
26.4k71168247
26.4k71168247
2
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
2
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
82
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
12
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
2
BTW, when you droppublic
, you lose any installed extensions.
– sudo
Jul 8 '16 at 16:53
|
show 4 more comments
2
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
2
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
82
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
12
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
2
BTW, when you droppublic
, you lose any installed extensions.
– sudo
Jul 8 '16 at 16:53
2
2
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
2
2
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
82
82
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
12
12
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
2
2
BTW, when you drop
public
, you lose any installed extensions.– sudo
Jul 8 '16 at 16:53
BTW, when you drop
public
, you lose any installed extensions.– sudo
Jul 8 '16 at 16:53
|
show 4 more comments
20 Answers
20
active
oldest
votes
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;
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
Note that this will not remove the system tables (such as those that begin withpg_
) as they are in a different schema,pg_catalog
.
– congusbongus
Aug 4 '14 at 7:07
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
Bringing this up from another answer you probably want to have aGRANT ALL ON SCHEMA public TO public;
after the create.
– Federico
Jan 20 '15 at 23:41
1
@Federico Why would you wantGRANT ALL
after the create?
– 425nesp
Apr 23 '15 at 3:56
|
show 5 more comments
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.
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
@jwg: also, because sometimes you don't have the permission todrop schema public cascade;
, but you almost always have the permissions to drop tables.
– berkes
May 6 '15 at 14:07
|
show 5 more comments
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)
4
Confirmed. The two line solution (drop
thencreate
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extragrant
is necessary.
– Jinghao Shi
Sep 15 '14 at 23:42
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
add a comment |
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.
NB: If you copy-paste topsql
add;
at the end
– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
Thanks for posting this! I couldn't use thedrop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)
– vdboor
Nov 22 '16 at 10:36
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
|
show 1 more comment
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
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
add a comment |
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';
add a comment |
drop schema public cascade;
should do the trick.
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
also you'll have to recreated again afterwards to add the tables back withCREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information
– mikermcneil
Nov 20 '13 at 23:15
add a comment |
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
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
add a comment |
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.
add a comment |
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'
add a comment |
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.
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
add a comment |
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; $$
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
add a comment |
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"
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
add a comment |
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
add a comment |
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
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after dorake db:create
, I run it. You can do the Steve tip and remove the codetable_name =
and change", "
for","
and#{ tables }
fo#{tables}
– Washington Botelho
Feb 4 '16 at 14:00
add a comment |
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>';
add a comment |
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"
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
add a comment |
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
)
add a comment |
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
.
add a comment |
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.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3327312%2fdrop-all-tables-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
20 Answers
20
active
oldest
votes
20 Answers
20
active
oldest
votes
active
oldest
votes
active
oldest
votes
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;
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
Note that this will not remove the system tables (such as those that begin withpg_
) as they are in a different schema,pg_catalog
.
– congusbongus
Aug 4 '14 at 7:07
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
Bringing this up from another answer you probably want to have aGRANT ALL ON SCHEMA public TO public;
after the create.
– Federico
Jan 20 '15 at 23:41
1
@Federico Why would you wantGRANT ALL
after the create?
– 425nesp
Apr 23 '15 at 3:56
|
show 5 more comments
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;
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
Note that this will not remove the system tables (such as those that begin withpg_
) as they are in a different schema,pg_catalog
.
– congusbongus
Aug 4 '14 at 7:07
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
Bringing this up from another answer you probably want to have aGRANT ALL ON SCHEMA public TO public;
after the create.
– Federico
Jan 20 '15 at 23:41
1
@Federico Why would you wantGRANT ALL
after the create?
– 425nesp
Apr 23 '15 at 3:56
|
show 5 more comments
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;
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;
edited Oct 19 '16 at 9:45


biniam
5,97833345
5,97833345
answered Dec 11 '12 at 15:52
Derek SlifeDerek Slife
12.4k11221
12.4k11221
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
Note that this will not remove the system tables (such as those that begin withpg_
) as they are in a different schema,pg_catalog
.
– congusbongus
Aug 4 '14 at 7:07
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
Bringing this up from another answer you probably want to have aGRANT ALL ON SCHEMA public TO public;
after the create.
– Federico
Jan 20 '15 at 23:41
1
@Federico Why would you wantGRANT ALL
after the create?
– 425nesp
Apr 23 '15 at 3:56
|
show 5 more comments
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
Note that this will not remove the system tables (such as those that begin withpg_
) as they are in a different schema,pg_catalog
.
– congusbongus
Aug 4 '14 at 7:07
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
Bringing this up from another answer you probably want to have aGRANT ALL ON SCHEMA public TO public;
after the create.
– Federico
Jan 20 '15 at 23:41
1
@Federico Why would you wantGRANT ALL
after the create?
– 425nesp
Apr 23 '15 at 3:56
82
82
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
Note that this will also delete all functions, views, etc defined in the public schema.
– Brad Koch
Mar 24 '13 at 0:15
3
3
Note that this will not remove the system tables (such as those that begin with
pg_
) as they are in a different schema, pg_catalog
.– congusbongus
Aug 4 '14 at 7:07
Note that this will not remove the system tables (such as those that begin with
pg_
) as they are in a different schema, pg_catalog
.– congusbongus
Aug 4 '14 at 7:07
29
29
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
This will create the schema w/ OWNER set to the user you're logged into psql as. This will conflict w/ applications who log in as a different user. In that case, you also need to run "ALTER SCHEMA public OWNER to postgres;" (or to whatever user your app uses to create tables)
– mgojohn
Oct 8 '14 at 15:49
13
13
Bringing this up from another answer you probably want to have a
GRANT ALL ON SCHEMA public TO public;
after the create.– Federico
Jan 20 '15 at 23:41
Bringing this up from another answer you probably want to have a
GRANT ALL ON SCHEMA public TO public;
after the create.– Federico
Jan 20 '15 at 23:41
1
1
@Federico Why would you want
GRANT ALL
after the create?– 425nesp
Apr 23 '15 at 3:56
@Federico Why would you want
GRANT ALL
after the create?– 425nesp
Apr 23 '15 at 3:56
|
show 5 more comments
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.
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
@jwg: also, because sometimes you don't have the permission todrop schema public cascade;
, but you almost always have the permissions to drop tables.
– berkes
May 6 '15 at 14:07
|
show 5 more comments
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.
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
@jwg: also, because sometimes you don't have the permission todrop schema public cascade;
, but you almost always have the permissions to drop tables.
– berkes
May 6 '15 at 14:07
|
show 5 more comments
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.
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.
edited Oct 29 '12 at 17:03
answered Jul 24 '10 at 23:29
Pablo Santa CruzPablo Santa Cruz
133k24198251
133k24198251
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
@jwg: also, because sometimes you don't have the permission todrop schema public cascade;
, but you almost always have the permissions to drop tables.
– berkes
May 6 '15 at 14:07
|
show 5 more comments
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
@jwg: also, because sometimes you don't have the permission todrop schema public cascade;
, but you almost always have the permissions to drop tables.
– berkes
May 6 '15 at 14:07
7
7
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
I think you meant: You can write a query like this... ...And then run the output of the query
– Vinko Vrsalovic
Jul 24 '10 at 23:31
5
5
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables; will make sure tables with uppercase are also properly dropped.
– Ivo van der Wijk
Apr 19 '12 at 14:03
11
11
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
the clause "where schemaname = 'public'" that LenW added in his answer can be very useful to reduce the scope of deletion to only the database you managed and not the system's ones
– Guillaume Gendre
Oct 29 '12 at 16:36
2
2
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
Love it! This is a great bit of stuff.
– Nicholas DiPiazza
Jan 26 '13 at 0:40
6
6
@jwg: also, because sometimes you don't have the permission to
drop schema public cascade;
, but you almost always have the permissions to drop tables.– berkes
May 6 '15 at 14:07
@jwg: also, because sometimes you don't have the permission to
drop schema public cascade;
, but you almost always have the permissions to drop tables.– berkes
May 6 '15 at 14:07
|
show 5 more comments
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)
4
Confirmed. The two line solution (drop
thencreate
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extragrant
is necessary.
– Jinghao Shi
Sep 15 '14 at 23:42
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
add a comment |
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)
4
Confirmed. The two line solution (drop
thencreate
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extragrant
is necessary.
– Jinghao Shi
Sep 15 '14 at 23:42
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
add a comment |
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 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)
answered Jan 21 '14 at 0:38
UserUser
23.9k51139219
23.9k51139219
4
Confirmed. The two line solution (drop
thencreate
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extragrant
is necessary.
– Jinghao Shi
Sep 15 '14 at 23:42
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
add a comment |
4
Confirmed. The two line solution (drop
thencreate
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extragrant
is necessary.
– Jinghao Shi
Sep 15 '14 at 23:42
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
4
4
Confirmed. The two line solution (
drop
then create
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant
is necessary.– Jinghao Shi
Sep 15 '14 at 23:42
Confirmed. The two line solution (
drop
then create
) used to work on PostgreSQL 9.1. After upgrading to 9.3, the two extra grant
is necessary.– Jinghao Shi
Sep 15 '14 at 23:42
4
4
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
One more confirm: using Django, I got the same error; I needed to run those grants before django could interact with the database.
– rjh
Apr 23 '15 at 8:21
2
2
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
This worked perfectly, except that I also needed to re-install some extensions: CREATE EXTENSION IF NOT EXISTS hstore; CREATE EXTENSION IF NOT EXISTS pgcrypto;
– shacker
Oct 31 '16 at 20:33
add a comment |
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.
NB: If you copy-paste topsql
add;
at the end
– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
Thanks for posting this! I couldn't use thedrop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)
– vdboor
Nov 22 '16 at 10:36
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
|
show 1 more comment
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.
NB: If you copy-paste topsql
add;
at the end
– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
Thanks for posting this! I couldn't use thedrop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)
– vdboor
Nov 22 '16 at 10:36
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
|
show 1 more comment
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.
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.
edited Aug 9 '16 at 9:43
answered Mar 15 '16 at 22:21


Piotr FindeisenPiotr Findeisen
5,06611538
5,06611538
NB: If you copy-paste topsql
add;
at the end
– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
Thanks for posting this! I couldn't use thedrop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)
– vdboor
Nov 22 '16 at 10:36
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
|
show 1 more comment
NB: If you copy-paste topsql
add;
at the end
– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
Thanks for posting this! I couldn't use thedrop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)
– vdboor
Nov 22 '16 at 10:36
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
NB: If you copy-paste to
psql
add ;
at the end– igo
Aug 7 '16 at 8:14
NB: If you copy-paste to
psql
add ;
at the end– igo
Aug 7 '16 at 8:14
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
@igo, thanks, i've updated the code.
– Piotr Findeisen
Aug 9 '16 at 9:43
4
4
Thanks for posting this! I couldn't use the
drop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)– vdboor
Nov 22 '16 at 10:36
Thanks for posting this! I couldn't use the
drop schema
trick as the user was not owner of the schema, only of the tables. This one worked though :)– vdboor
Nov 22 '16 at 10:36
9
9
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
This should be the accepted answer imho. Thanks :)
– Sankar
Apr 4 '17 at 8:01
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
Very clean and specific... great solution, and should be the accepted too - you can even add to the where clause to limit tables you want to keep, as in those needed by extensions such as PostGIS...
– DPSSpatial
Nov 18 '17 at 15:46
|
show 1 more comment
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
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
add a comment |
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
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
add a comment |
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
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
edited Sep 17 '18 at 18:33
answered Dec 1 '15 at 7:17
a_horse_with_no_namea_horse_with_no_name
294k46449543
294k46449543
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
add a comment |
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
2
2
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
This dropped all schemas owned by the user (which I didn't want to do).
– Peter L
May 7 '18 at 22:37
1
1
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
@PeterL: which is clearly documented in the manual, but I edited my post to make it clear that "everything" really means everything
– a_horse_with_no_name
May 8 '18 at 6:11
add a comment |
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';
add a comment |
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';
add a comment |
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';
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';
edited Jun 4 '12 at 20:15
MAbraham1
1,11842135
1,11842135
answered May 1 '12 at 6:27
LenWLenW
2,33311924
2,33311924
add a comment |
add a comment |
drop schema public cascade;
should do the trick.
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
also you'll have to recreated again afterwards to add the tables back withCREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information
– mikermcneil
Nov 20 '13 at 23:15
add a comment |
drop schema public cascade;
should do the trick.
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
also you'll have to recreated again afterwards to add the tables back withCREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information
– mikermcneil
Nov 20 '13 at 23:15
add a comment |
drop schema public cascade;
should do the trick.
drop schema public cascade;
should do the trick.
edited Oct 28 '12 at 1:17
answered Oct 27 '12 at 21:11
Joe Van DykJoe Van Dyk
4,65274567
4,65274567
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
also you'll have to recreated again afterwards to add the tables back withCREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information
– mikermcneil
Nov 20 '13 at 23:15
add a comment |
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
also you'll have to recreated again afterwards to add the tables back withCREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information
– mikermcneil
Nov 20 '13 at 23:15
10
10
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
Note that this will also delete all functions, views, etc defined in the public schema.
– Joe Van Dyk
Jan 9 '13 at 22:23
4
4
also you'll have to recreated again afterwards to add the tables back with
CREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information– mikermcneil
Nov 20 '13 at 23:15
also you'll have to recreated again afterwards to add the tables back with
CREATE SCHEMA public;
. Also see stackoverflow.com/a/14286370 for more information– mikermcneil
Nov 20 '13 at 23:15
add a comment |
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
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
add a comment |
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
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
add a comment |
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
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
answered Oct 23 '12 at 15:03
Tim DigginsTim Diggins
3,15032139
3,15032139
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
add a comment |
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
Thanks, it works for me.
– Saeed Zarinfam
Nov 23 '17 at 9:54
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
edited Jul 16 '12 at 3:13
answered Jul 13 '12 at 0:12
Mark LawrenceMark Lawrence
23126
23126
add a comment |
add a comment |
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'
add a comment |
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'
add a comment |
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'
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'
answered Jul 7 '17 at 23:38
AdéAdé
14029
14029
add a comment |
add a comment |
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.
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
add a comment |
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.
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
add a comment |
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.
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.
answered Oct 25 '12 at 9:31
Piotr KochańskiPiotr Kochański
15.2k56070
15.2k56070
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
add a comment |
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
1
1
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
works line a charm. I picked this because I liked hardcoding the db connection info - last thing I want to do is hitting the wrong db! and, also, my table list is a moving target.
– JL Peyret
Oct 20 '17 at 17:17
add a comment |
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; $$
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
add a comment |
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; $$
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
add a comment |
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; $$
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; $$
answered Mar 2 '18 at 17:16


Luca PericoLuca Perico
460412
460412
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
add a comment |
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
That sql failed for me. I used SELECT concat('drop table ',tablename, ' cascade ;') AS drop_table_sql FROM pg_tables WHERE schemaname = 'public'
– Keith John Hutchison
May 15 '18 at 11:15
What error did you get?
– Luca Perico
May 16 '18 at 13:56
What error did you get?
– Luca Perico
May 16 '18 at 13:56
1
1
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
Must have been something I did wrong, Luca. I just tried it again and it worked.
– Keith John Hutchison
May 17 '18 at 0:31
add a comment |
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"
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
add a comment |
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"
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
add a comment |
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"
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"
answered Dec 18 '12 at 21:44


JamieJamie
1,3901316
1,3901316
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
add a comment |
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
should should be #!/bin/sh
– Good Person
May 11 '14 at 8:04
add a comment |
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
add a comment |
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
add a comment |
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
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
edited Aug 12 '15 at 23:28
answered Jun 7 '15 at 12:48
Muayyad AlsadiMuayyad Alsadi
1,036819
1,036819
add a comment |
add a comment |
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
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after dorake db:create
, I run it. You can do the Steve tip and remove the codetable_name =
and change", "
for","
and#{ tables }
fo#{tables}
– Washington Botelho
Feb 4 '16 at 14:00
add a comment |
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
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after dorake db:create
, I run it. You can do the Steve tip and remove the codetable_name =
and change", "
for","
and#{ tables }
fo#{tables}
– Washington Botelho
Feb 4 '16 at 14:00
add a comment |
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
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
answered Apr 21 '14 at 8:15
the-teacherthe-teacher
311317
311317
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after dorake db:create
, I run it. You can do the Steve tip and remove the codetable_name =
and change", "
for","
and#{ tables }
fo#{tables}
– Washington Botelho
Feb 4 '16 at 14:00
add a comment |
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after dorake db:create
, I run it. You can do the Steve tip and remove the codetable_name =
and change", "
for","
and#{ tables }
fo#{tables}
– Washington Botelho
Feb 4 '16 at 14:00
1
1
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
It might be simpler/safer to say AND table_schema = 'public' rather than NOT IN that list.
– Steve
Sep 30 '14 at 2:15
For some reason my schema was created with populated data. This rake works. So after do
rake db:create
, I run it. You can do the Steve tip and remove the code table_name =
and change ", "
for ","
and #{ tables }
fo #{tables}
– Washington Botelho
Feb 4 '16 at 14:00
For some reason my schema was created with populated data. This rake works. So after do
rake db:create
, I run it. You can do the Steve tip and remove the code table_name =
and change ", "
for ","
and #{ tables }
fo #{tables}
– Washington Botelho
Feb 4 '16 at 14:00
add a comment |
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>';
add a comment |
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>';
add a comment |
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 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>';
answered Jan 9 '18 at 6:13


pooyapooya
9615
9615
add a comment |
add a comment |
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"
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
add a comment |
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"
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
add a comment |
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"
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"
answered Apr 24 '14 at 12:53


martinseenermartinseener
313
313
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
add a comment |
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=
psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
Great script... just used it and worked like a charm. I also added a line for sequences: SEQUENCES=
psql -d $PGDB -t --command "SELECT string_agg(sequence_name, ',') FROM information_schema.sequences WHERE sequence_schema='public' AND sequence_catalog='$PGDB'"
– raminr
Jun 14 '15 at 17:53
add a comment |
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
)
add a comment |
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
)
add a comment |
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
)
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
)
edited Dec 2 '15 at 19:47
johntellsall
8,53522626
8,53522626
answered Aug 15 '13 at 20:18
LewisLewis
211
211
add a comment |
add a comment |
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
.
add a comment |
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
.
add a comment |
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
.
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
.
edited Apr 17 '18 at 20:48
answered Apr 17 '18 at 20:36
JL PeyretJL Peyret
2,9871630
2,9871630
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Dec 28 '18 at 21:29
RichardPRichardP
47257
47257
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3327312%2fdrop-all-tables-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
ZeoJFzKh90kZ0Hg0i
2
What command line are you talking about? For all we know you're looking for a Windows PowerShell implementation.
– Greg Smith
Jul 25 '10 at 1:24
2
Sorry. Working on Unix, after typing 'psql' at the command line - so the psql command-line environment itself.
– AP257
Jul 25 '10 at 8:34
82
DROP SCHEMA public CASCADE; -- shudder
– wildplasser
Jun 4 '12 at 20:18
12
@0fnt you will have to do 'CREATE SCHEMA public;' to add new tables again (found out the hard way)
– nym
Jun 9 '15 at 0:04
2
BTW, when you drop
public
, you lose any installed extensions.– sudo
Jul 8 '16 at 16:53