Postgres: delete all tables or cascade a delete?

Question

Is it possible either to delete all rows from all tables with a single command in postgres (without destroying the database), or to cascade a delete in postgres?

If not, then how can I reset my test database?

Answer 1

is it possible either to delete all rows from all tables with a single command in postgres

You are right, the answer is NO

You can define cascading foreign keys that will delete all referencing rows if the "parent" is deleted. But that is an attribute of the foreign key, nothing you can specify with the DELETE statement

if not - wow.

What's that supposed to mean?

On second thought: what are you trying to achieve?

I have the suspicion that you are trying to "reset" e.g. a test database. In that case the PostgreSQL approach would be:

• Create a database that contains everything (tables, views, indexes etc) you need in a new database (call it e.g. my_template)
• To reset your current test-DB, do a DROP DATABASE testdb and then re-create the test database using CREATE DATABASE testdb TEMPLATE my_template

The newly created testdb will have all tables defined in my_template. That is probably a lot faster than deleting all rows.