Truncating all tables in a Postgres database

FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here's the script:

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$DECLARE    statements CURSOR FOR        SELECT tablename FROM pg_tables        WHERE tableowner = username AND schemaname = 'public';BEGIN    FOR stmt IN statements LOOP        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';    END LOOP;END;$$ LANGUAGE plpgsql;

This creates a stored function (you need to do this just once) which you can afterwards use like this:

SELECT truncate_tables('MYUSER');

Explicit cursors are rarely needed in plpgsql. Use the simpler and faster implicit cursor of a FOR loop:

Note: Since table names are not unique per database, you have to schema-qualify table names to be sure. Also, I limit the function to the default schema 'public'. Adapt to your needs, but be sure to exclude the system schemas pg_* and information_schema.

Be very careful with these functions. They nuke your database. I added a child safety device. Comment the RAISE NOTICE line and uncomment EXECUTE to prime the bomb ...

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)  RETURNS void AS$func$DECLARE   _tbl text;   _sch text;BEGIN   FOR _sch, _tbl IN       SELECT schemaname, tablename      FROM   pg_tables      WHERE  tableowner = _username      AND        -- dangerous, test before you execute!      RAISE NOTICE '%',  -- once confident, comment this line ...      -- EXECUTE         -- ... and uncomment this one         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);   END LOOP;END$func$ LANGUAGE plpgsql;

format() requires Postgres 9.1 or later. In older versions concatenate the query string like this:

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

Single command, no loop

Since we can TRUNCATE multiple tables at once we don't need any cursor or loop at all:

• Passing table names in an array

Aggregate all table names and execute a single statement. Simpler, faster:

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)  RETURNS void AS$func$BEGIN   -- dangerous, test before you execute!   RAISE NOTICE '%',  -- once confident, comment this line ...   -- EXECUTE         -- ... and uncomment this one  (SELECT 'TRUNCATE TABLE '       || string_agg(format('%I.%I', schemaname, tablename), ', ')       || ' CASCADE'   FROM   pg_tables   WHERE  tableowner = _username   AND    schemaname = 'public'   );END$func$ LANGUAGE plpgsql;

Call:

SELECT truncate_tables('postgres');

Refined query

You don't even need a function. In Postgres 9.0+ you can execute dynamic commands in a DO statement. And in Postgres 9.5+ the syntax can be even simpler:

DO$func$BEGIN   -- dangerous, test before you execute!   RAISE NOTICE '%',  -- once confident, comment this line ...   -- EXECUTE         -- ... and uncomment this one   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'    FROM   pg_class    WHERE  relkind = 'r'  -- only tables    AND    relnamespace = 'public'::regnamespace   );END$func$;

About the difference between pg_class, pg_tables and information_schema.tables:

• How to check if a table exists in a given schema

About regclass and quoted table names:

• Table name as a PostgreSQL function parameter

For repeated use

Create a "template" database (let's name it my_template) with your vanilla structure and all empty tables. Then go through a DROP / CREATE DATABASE cycle:

DROP DATABASE mydb;CREATE DATABASE mydb TEMPLATE my_template;

This is extremely fast, because Postgres copies the whole structure on the file level. No concurrency issues or other overhead slowing you down.

If concurrent connections keep you from dropping the DB, consider:

• Force drop db while others may be connected

If I have to do this, I will simply create a schema sql of current db, then drop & create db, then load db with schema sql.

Below are the steps involved:

1) Create Schema dump of database (--schema-only)

pg_dump mydb -s > schema.sql

2) Drop database

drop database mydb;

3) Create Database

create database mydb;

4) Import Schema

psql mydb < schema.sql