How to delete all data in all tables in a PostGre datatabase

pgirardier
pgirardier used Ask the Experts™
on
Hello, I would like to delete all data from all tables from an existing PosteGre Database. I would like to do it  from within pgAdminIII. But I do not want to remove the tables, only the data from all tables. The number of tables is important, and I do not want to do a DELETE on each table. Thanks for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CEO/Programmer
Commented:
Databases keep information about tables, databases etc in other tables, This data about data are called meta data in PostgresSQL the tables starting with pg_ are metatables. you can query them as every other database, Just be sure that you do not change the data by accident because than the database is "done"

So in your case you can check the following "meta tables"
http://www.postgresql.org/docs/8.4/interactive/catalogs.html

I'm not using pgAdminUI, so I can't tell how to query tables there but in my case I just can excludea all the tables owned by postgres and then I just got the table I created
 select * from pg_tables where tableowner <> 'postgres';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+------------+----------+-------------
 public     | foo       | frido      |            | f          | f        | f
(1 Zeile)
 
 
 
So now I could run
delete from foo...

Open in new window

1.  If you can make a list of your tables by comma separator easily, you can use TRUNCATE command :

TRUNCATE yourschema.table1, yourschema.table2, yourschema.table3, yourschema.table4, .......... and so on.

2.  But if there are too many tables, you can dump the database without data, drop the database and then create new database and restore database again. So you get a new database without data in the tables:

Dump the database :
OSPrompt>pg_dump --schema-only yourdb > yourdb.sql

Drop and create :
OSPrompt>dropdb -U username yourdb
OSPrompt>createdb -U username yourdb

Restore :
OSPrompt>psql -U username -f yourdb.sql yourdb
1.  If you can make a list of your tables by comma separator easily, you can use TRUNCATE command in PostgreSQL Prompt:

yourdb#TRUNCATE yourschema.table1, yourschema.table2, yourschema.table3, yourschema.table4, .......... and so on.

2.  But if there are too many tables, you can dump the database without data, drop the database and then create new database and restore database again. So you get a new database without data in the tables:

Dump the database :
OSPrompt>pg_dump -U username --schema-only yourdb > yourdb.sql

Drop and create :
OSPrompt>dropdb -U username yourdb
OSPrompt>createdb -U username yourdb

Restore :
OSPrompt>psql -U username -f yourdb.sql yourdb
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
Ok, I can get the list of my 500 tables using "select tablename from pg_tables where schemaname = 'public'" in my case, but then how could I delete all the content?
Doing "delete from (select tablename from pg_tables where schemaname = 'public')" does not work obvsioulsy. How to iterate through the reqult of the select query to delete all content? Again, I have hundreds of tables. Thanks
fridomCEO/Programmer
Commented:
Well you probably have to write a loop in pgsql to call delete on every table, see e.g
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

That's not standard SQL but it will work in Postgres

But pleas write a test routine first before you really do the deletion. And be really careful with your query.

Regards
Friedrich
You will not be able to do a TRUNCATE if you write a loop in pgsql.  Postgres functions run within an implied transaction and as you can't ROLLBACK a TRUNCATE it will not run.

DO NOT USE DELETE * FROM tabel_name in this context.  DELETE simply marks the records as "unavailable"; it does NOT free up space and after a few months you are going to wonder what happened to your disk space.

PGAdmin III 10.x has it's own scripting language.  As you mentioned that you wanted a solution you could run from within PGAdmin, you may want to play with that new feature.

No offense to fridom, but I think the awarded points were misplaced.  The recommendation to use PGSQL for this purpose, while it will work, is problematic in that it does not free up the space and will eventually affect your disk space and even query effiency.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial