Truncating all tables in a Postgres database

postgresql plpgsql dynamic-sql truncate database-table
Sig picture Sig · May 13, 2010 · Viewed 160.2k times · Source

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 can't see a way to execute them programmatically once I have them.

Answer

Henning picture Henning · May 13, 2010

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

Related questions

Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select * from quote_ident($1) where quote_ident($1).…

Read More
PostgreSQL - Writing dynamic sql in stored procedure that returns a result set

How can I write a stored procedure that contains a dynamically built SQL statement that returns a result set? Here is my sample code: CREATE OR REPLACE FUNCTION reporting.report_get_countries_new ( starts_with varchar, ends_with varchar ) RETURNS …

Read More
Function to loop through and select data from multiple tables

I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria. I could do this with a bunch of UNION queries, but the number …

Read More