How to drop all schemas in PostgreSQL with SQL query?

Question

I need to drop all the schemas in my database except public, information_schema and those LIKE 'pg_%'.

Here's what I've found: (this variant doesn't seem to work)

   CREATE OR REPLACE FUNCTION drop_all () 
   RETURNS VOID  AS
   $$
   DECLARE rec RECORD; 
   BEGIN
       -- Get all the schemas
        FOR rec IN
        SELECT DISTINCT schemaname
         FROM pg_catalog.pg_tables
         -- You can exclude the schema which you don't want to drop by adding another condition here
         WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'public' 
         AND schemaname != 'information_schema'
           LOOP
             EXECUTE 'DROP SCHEMA ' || rec.schemaname || ' CASCADE'; 
           END LOOP; 
           RETURN; 
   END;
   $$ LANGUAGE plpgsql;

   SELECT drop_all();

Another variant: (this one probably works but still crashes my app)

SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
FROM   pg_namespace WHERE nspname != 'public'
AND nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema';

So, I wanted to make sure that the queries are correct or, hopefully, find another variant of the query.

Could anybody help me with finding another query for doing that?

Germanism

"Doesn't work" isn't helpful, nor is "stil crashes my app". What doesn't work and what errors are you getting. Start by demonstrating it in psql. – Rotorua

Answer 1

It drops only schema with any table, because you are using the query:

 SELECT DISTINCT schemaname
         FROM pg_catalog.pg_tables

so schemas without any table are not dropped.

You have to use a different query

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'

Septuagint