asked on

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

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.

ASKER CERTIFIED SOLUTION

F. Dominicus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

a_didit_m

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

a_didit_m

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

pgirardier

ASKER

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

SOLUTION

F. Dominicus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toolsmythe

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.