Truncating all tables in a 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 can t see a way to execute them programmatically once I have them

User · Answer

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

User · Answer

Guys the better and clean way is to    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  lt  schema sql  It  s work for me   Have a nice day  Hiram Walker

User · Answer

If I have to do this  I will simply create a schema sql of current db  then drop  amp  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  gt  schema sql  2  Drop database  drop database mydb   3  Create Database  create database mydb   4  Import Schema   psql mydb  lt  schema sql

User · Answer

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

User · Answer

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

User · Answer

Just execute the query bellow  DO    DECLARE     r RECORD  BEGIN     FOR r IN  SELECT tablename FROM pg tables WHERE schemaname   current schema    LOOP         EXECUTE  TRUNCATE TABLE      quote ident r tablename             END LOOP  END

User · Answer

Cleaning AUTO INCREMENT version   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             IF EXISTS               SELECT column name              FROM information schema columns              WHERE table name quote ident stmt tablename  and column name  id            THEN            EXECUTE  ALTER SEQUENCE      quote ident stmt tablename       id seq RESTART WITH 1           END IF       END LOOP  END     LANGUAGE plpgsql

User · Answer

You can do this with bash also      bin bash PGPASSWORD    psql -h 127 0 0 1 -Upostgres sng --tuples-only --command  SELECT  TRUNCATE TABLE      schemaname            tablename        FROM pg tables WHERE schemaname in   cms test    ids test    logs test    sps test        tr    n         xargs -I   psql -h 127 0 0 1 -Upostgres sng --command        You will need to adjust schema names  passwords and usernames to match your schemas

User · Answer

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

User · Answer

For removing the data and preserving the table-structures in pgAdmin you can do    Right-click database -  backup  select  Schema only  Drop the database Create a new database and name it like the former Right-click the new database -  restore -  select the backup  select  Schema only

User · Answer

If you can use psql you can use  gexec meta command to execute query output   SELECT     format  TRUNCATE TABLE  I  I   ns nspname  c relname    FROM pg namespace ns    JOIN pg class c ON ns oid   c relnamespace   JOIN pg roles r ON r oid   c relowner   WHERE     ns nspname    table schema  AND                               -- add table schema criteria      r rolname    table owner  AND                                 -- add table owner criteria     ns nspname NOT IN   pg catalog    information schema   AND    -- exclude system schemas     c relkind    r  AND                                           -- tables only     has table privilege c oid   TRUNCATE                          -- check current user has truncate privilege    gexec    Note that  gexec is introduced into the version 9 6

[postgresql] Truncating all tables in a Postgres database

Single command, no loop

Refined query

For repeated use

Examples related to postgresql

Examples related to plpgsql

Examples related to dynamic-sql

Examples related to truncate

Examples related to database-table