Postgres: delete all tables or cascade a delete?

Question

From Googling, I'm pretty sure the answer to this is "no", but anyway.

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 - wow.

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.

Answer 2

You could write a stored procedure that selects all tables in all schemas and then does a DELETE CASCADE or TRUNCATE on these tables. It's just a few lines of pl/pgsql-code, not a big deal.

Edit: This will do the trick, but be carefull! :

CREATE OR REPLACE FUNCTION truncate_all() RETURNS void LANGUAGE plpgsql AS
$$
DECLARE
    row record;
    query   text;
BEGIN
    query := 'SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN(''pg_catalog'', ''information_schema'') AND table_type = ''BASE TABLE''';

    FOR row IN EXECUTE query LOOP
        EXECUTE 'TRUNCATE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE;';
    END LOOP;

    RETURN;
END;
$$;

-- execute:
SELECT truncate_all();