How do I remove all tables and not the schema in Postgresql?

Question

I have a schema assigned to my user (jason). I can't remove the schema as I don't have permissions to do so. Is there a nice way to remove each tables, data, everything in a schema and make it as if I had a freshly created schema. Basically the same effect as:

drop schema jason cascade;
create schema jason;

But without the actually dropping the schema.

I guess the best way would be to generate the list of DROP TABLE statements from the infomration_schema.tables and then run all them at once. — Ispirer SQLWays Migrations
– Ispirer SQLWays Migrations, Commented Jul 14, 2015 at 12:10

Michel Milezzi · Accepted Answer · 2018-04-03 12:46:42Z

18

The accepted answer is great, but you can do this in just one step taking advantage from anonymous blocks (PosgreSQL 9.0+):

DO $$
DECLARE 
    r record;
BEGIN
    FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'public'
    LOOP
        RAISE INFO 'Dropping table %.%', r.schemaname, r.tablename;
        EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
    END LOOP;
END$$;

edited Apr 3, 2018 at 12:46

user330315

answered Apr 26, 2017 at 13:01

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank N Stein · Accepted Answer · 2021-01-18 18:05:34Z

9

Try this from psql:

-- Turn off headers:
\t
-- Use SQL to build SQL:
select 'drop table if exists "' || tablename || '" cascade;' 
  from pg_tables
 where schemaname = 'jason'; 
-- If the output looks good, write it to a file and run it:
\g out.tmp
\i out.tmp

from stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql

edited Jan 18, 2021 at 18:05

answered Jul 14, 2015 at 13:08

Frank N Stein

2,5983 gold badges24 silver badges30 bronze badges

3 Comments

Encompass Over a year ago

I would also like to comment that this only creates the set of commands that you need to run after this to make delete the table.

Frank N Stein Over a year ago

Yes and NO, yes create the command set and run they.

Frank N Stein Over a year ago

Alternative: use \gexec instead of \g & \i

Collectives™ on Stack Overflow

How do I remove all tables and not the schema in Postgresql?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related