How to truncate data from all tables in a database

Many times when we are working in development environment, we would like to start from scratch but keep the schema intact, instead of manually deleting data from one table at a time, you can use automate this.

PostgreSQL doesn't provide a single command to truncate all tables in a database. However, you can use a PL/pgSQL code block to achieve this.

The PL/pgSQL Approach

Here's a PostgreSQL query to truncate all tables in a specified schema (usually 'public'):

please be cautious while running this query, you will loose all your data from database, recommended only for local and dev servers.

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP -- or other schema name
        EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE'; -- or other schema name
    END LOOP;
END $$;