Drop all tables in a Postgres schema

This query drops all tables in a given schema. Replace my-schema-name with the name of your schema. In Supabase, the default schema is public.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

1
do $$ declare
2
    r record;
3
begin
4
    for r in (select tablename from pg_tables where schemaname = 'my-schema-name') loop
5
        execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
6
    end loop;
7
end $$;

This query works by listing out all the tables in the given schema and then executing a drop table for each (hence the for... loop).