Truncating all tables in a Postgres database

Asked 13/5, 2010 at 18:4 Answered 28/11, 2022 at 8:21

Solved postgresql plpgsql dynamic-sql truncate database-table

220

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.

Mackenie answered 13/5, 2010 at 18:4 Comment(0)

304

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

Arrest answered 13/5, 2010 at 19:2 Comment(10)

Had to rejig a little but after that it worked like a charm! I've never used plpgsql before so this would've taken me ages. Thanks! For anyone that needs it I've added the code I ended up using to the bottom of this post. – Mackenie 13/5, 2010 at 21:32

Sorry, I was probably thinking in Oracle PL/SQL :( I fixed the syntax error in my code above. – Arrest 14/5, 2010 at 13:18

you can also move the SELECT statement directly to FOR loop. DECLARE r RECORD; then for loop: FOR r IN SELECT tablename FROM pg_tables LOOP – Holoblastic 14/5, 2010 at 13:55

I would add CASCADE to TRUNCATE TABLE – Hellenize 20/5, 2010 at 11:35

In fact, to truncate ALL tables, the CASCADE clause is required! – Alps 2/8, 2011 at 8:52

Just remember that you need to restart your sequences (auto increment) or your new data will start with the previous max id number... – Gnosticize 25/3, 2015 at 19:8

@Arrest I realize this is an older question, but would adding RESTART IDENTITY be a good addition to the query? – Biagio 16/10, 2017 at 7:50

OMG!! I just truncated all my tables in "public" schema.... pls add another parameter of "schema" so that the function truncates tables only on the schema that is provided! – Thermostatics 1/12, 2017 at 13:7

@Thermostatics see the question: ALL TABLES in a database! – Maxinemaxiskirt 15/3, 2019 at 11:0

where to mention database name? – Kostival 25/3, 2023 at 15:53

130

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

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
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      -- 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$;

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
  LANGUAGE plpgsql 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$;

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
$do$
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
$do$;

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

Ceramic answered 22/8, 2012 at 22:1 Comment(12)

It's worth noting that this last function wiped ALL databases. Not just the currently connected one.... yeah... call me naiive but that really wasn't clear from this post. – Misogyny 10/9, 2015 at 0:40

@Amalgovinus: Which last function? None of the functions in my answer touch anything outside the current database (except for DROP DATABASE mydb, obviously). Are you confusing schemas with databases, maybe? – Ceramic 10/9, 2015 at 1:25

The very last one with DO $func$. I used the same schema name in two different databases, so it looks like my data was dropped from both. so yes, I was confusing those two – Misogyny 10/9, 2015 at 1:33

@Amalgovinus: No, that's impossible. The DO command (like any other SQL statement) is executed in the current database exclusively. Postgres has no way to access other databases in the same transaction. You would have to use dblink or FDW to do that. But it does affect all schemas in the current database - unless you add WHERE t.schemaname = 'public' to restrict the effect to one particular schema in this particular case. – Ceramic 10/9, 2015 at 1:36

Really nice to know about those templates. This can me useful even in automated tests scenarios, where a database reset/preparation may be needed. – Grecism 16/3, 2017 at 19:51

I fixed your refined query, it had a wayward extra semicolon that my postgres choked on. ERROR: mismatched parentheses at or near ";" LINE 8: ... AND relnamespace = 'public'::regnamespace; – Arcadia 2/8, 2018 at 11:4

Thanks for great answer, I'm using "Single command, no loop" which returns the TRUNCATE command, how should I go about executing it? – Cribbing 5/11, 2018 at 22:30

@Cribbing i was confused by this at first too, comment the RAISE line and uncomment the EXECUTE one. – Webfoot 7/10, 2019 at 14:40

In your Refined query example, why is EXECUTE commented out? I can't seem to get that method to work. Without the EXECUTE it just makes a string of the TRUNCATE TABLE ... command but doesn't run it. Uncommenting the EXECUTE results in syntax error at or near "SELECT". I ended up having to delete the RAISE NOTICE line, and uncomment EXECUTE to get it to run. – Heida 17/4, 2020 at 12:52

@CodingWithSpike: You may have missed the note at the top:

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 ...

– Ceramic 19/8, 2020 at 22:0

the suggested queue is full, but @ErwinBrandstetter you should take a look at them. in your last edit you deleted the vital LOOP keyword in the main answer – Decide 24/3, 2022 at 20:22

@SampsonCrowley: Thanks for pointing out! I fixed the collateral damage from my previous edit. – Ceramic 25/3, 2022 at 22:30

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

Eliathan answered 5/8, 2014 at 10:21 Comment(0)

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 $$;

Lindsylindy answered 3/8, 2020 at 9:53 Comment(5)

I have tried many other solutions but only this one works. – Midwifery 13/5, 2021 at 11:58

I had foreign key constraint in few tables in schema on line number 5. EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || ' cascade'; – Assuage 4/3, 2022 at 13:40

Simple is best 😍 – Keyboard 14/3, 2022 at 13:40

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) || ' CASCADE'; END LOOP; END $$; // use this for CASCADE – Calmas 29/3, 2022 at 5:57

is there any way to run this like psql database_name -c ( command) because it give errors when I try. Query works when I login into database and paste it but I would love to run it without going into database – Ever 23/11, 2022 at 12:32

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.

Bora answered 13/5, 2010 at 20:35 Comment(0)

One thing that I don't see here is truncating and then resetting sequences. Note that a simple truncate like all that have been given here will just truncate the tables, but will leave sequences at their pre-truncate values. To reset the sequences to their start values when you truncate do:

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

you can just add that RESTART IDENTITY to any of the answers you fancy, no need to repeat that here. CASCADE is there for any foreign key constraints you may face.

Heins answered 21/8, 2022 at 17:41 Comment(0)

Simply, you can run this piece of SQL :

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) || ' CASCADE';
  END LOOP;
END $$;

Gemology answered 28/11, 2022 at 8:21 Comment(0)

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 < schema.sql

It´s work for me!

Have a nice day. Hiram Walker

Snare answered 5/6, 2018 at 15:1 Comment(0)

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;

Madox answered 17/8, 2017 at 19:42 Comment(0)

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)

Parnassus answered 13/5, 2010 at 18:6 Comment(0)

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.

Nomarch answered 9/4, 2013 at 13:56 Comment(0)

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"

Mitchmitchael answered 29/3, 2017 at 12:9 Comment(0)

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

Dahomey answered 17/3, 2020 at 12:23 Comment(0)

-2

You can use something like this to get all truncate queries.

SELECT 'TRUNCATE TABLE ' ||  table_name || ';' 
  FROM information_schema.tables
 WHERE table_schema='schema_name'
   AND table_type='BASE TABLE';

Financial answered 26/4, 2022 at 7:7 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Single command, no loop

Refined query

For repeated use

Recommended topics

Hot tags