11

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

Thirumal
  • 2,398
  • 3
  • 13
  • 24
  • 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: http://stackoverflow.com/questions/2117708/how-to-empty-a-sql-database –  Oct 26 '16 at 09:35

1 Answers1

23

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