6

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

12

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

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