current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 0 down vote favorite

This question already has an answer here:

Is there any SQL query to delete all rows from all tables in postgresql?

share|improve this question

marked as duplicate by TypoCubeᵀᴹ sql Nov 3 at 19:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from serverfault.com Nov 3 at 19:20

This question came from our site for system and network administrators.
1  
Yes, you have do backup the schema (dump), delete and recreate the database (or just create a new one) and then restore the schema. This has already been asked on stackoverflow: stackoverflow.com/questions/2117708/how-to-empty-a-sql-datab‌​ase – Broco Oct 26 at 9:35
up vote 2 down vote accepted

Method 1: create a file with a set of sql-statements to truncate each table, then execute those statements from the file.

testdb=# \t
Tuples only is on.
testdb=# \o truncate_all_tables.sql
testdb=# SELECT 'TRUNCATE ' || table_name || ';' FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';
testdb=# \o
testdb=# \t
Tuples only is off.
testdb=# \i truncate_all_tables.sql
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE

Method 2: create a database dump with schema only, recreate the database and restore the dump.

# pg_dump -U postgres -v -Fc -s -f testdb.dump testdb
# dropdb -U postgres testdb
# createdb -U postgres testdb
# pg_restore -U postgres -v -d testdb testdb.dump
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.