How I do destroy all tables for schema in PostgreSQL

Question

I have some tables in schema public's;

I want destroy all tables at schema. Like DROP TABLE public.*

Possible duplicate of [Drop all tables in postgresql?](http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql) — Lukasz Szozda, Nov 12 '15 at 12:45
If you have found a solution for your own question, you should write an answer to yourself. — Lucas Azevedo, Nov 12 '15 at 13:32
`drop owned by your_user_name`. Or `drop schema public cascade; create schema public` if there are tables owned by different users. — , Nov 12 '15 at 14:56

score 3 · Answer 1 · answered Nov 12 '15 at 13:46

3

I usually do it like this (I've commented execution of the query, it will only be printed):

do $$
declare
    rec record;
    query text;
begin
    for rec in select * from pg_tables where schemaname = 'public'
    loop
        query = format('drop table %s.%s', rec.schemaname, rec.tablename);
        raise notice '%', query;
        --execute query;
    end loop;
end
$$ language plpgsql;

I use cool DO thing when I just want to execute some code but don't want to create a stored procedure.

answered Nov 12 '15 at 13:46

stas.yaranov

1,797
10
17

Thanks! this my wanna answer. – Yingce Nov 12 '15 at 14:33
You're welcome! If this really works for you then you should mark this answer as correct. – stas.yaranov Nov 12 '15 at 17:30

score 0 · Answer 2 · answered Jun 13 '17 at 14:41

0

If all of the tables are in the same schema (public):

DROP SCHEMA public CASCADE;

CREATE SCHEMA public;

answered Jun 13 '17 at 14:41

alibaba

1,623
2
12
13

How I do destroy all tables for schema in PostgreSQL

2 Answers2