How to truncate all tables in a PosgreSQL database

below are two options you could use to truncate all tables, delete all data in a PosgreSQL.

First option is simply creating a schema template of current db and then drop & create db. Finally, loading your fresh database with exported schema template.

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

The second option is using the script below to create a function called truncate_schema(). And when you need to truncate all tables in a schema, Select the function with the argument is the schema name:

SELECT truncate_schema('schema_name')

here is the script to create the function:

CREATE OR REPLACE FUNCTION truncate_schema(_schema character varying)
RETURNS void AS
$BODY$
declare
selectrow record;
begin
for selectrow in
select ‘TRUNCATE TABLE ‘ || quote_ident(_schema) || ‘.’ ||quote_ident(t.table_name) || ‘ CASCADE;’ as qry
from (
SELECT table_name
FROM information_schema.tables
WHERE table_type = ‘BASE TABLE’ AND table_schema = _schema
)t
loop
execute selectrow.qry;
end loop;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION truncate_schema(character varying)
OWNER TO postgres;