Is there any SQL query to delete all rows from all tables in postgresql?
1
-
1Yes, 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-databaseBroco– Broco2016-10-26 09:35:44 +00:00Commented Oct 26, 2016 at 9:35
Add a comment
|
1 Answer
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