Truncate all tables in postgres database

Question

I regularly need to delete all the data from my postgresql database before a rebuild. How would I do this directly in SQL?

At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute:

SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';

but I cant see a way to execute them programatically once I have them...

Thanks in advance

Answer 1

Could you use dynamic SQL to execute each statement in turn? You would probably have to write a PL/pgSQL script to do this.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (section 38.5.4. Executing Dynamic Commands)

Answer 2

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) || ';';
    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');

HTH!

Answer 3

In this case it would probably be better to just have an empty database that you use as a template and when you need to refresh, drop the existing database and create a new one from the template.

Answer 4

Slightly adapted...

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