DO $$ DECLARE r RECORD; BEGIN FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE'; END LOOP; END $$;
This query will delete all available tables for current user and schema, without needs to have SCHEMA privileges.
The most popular solution via:
DROP SCHEMA public CASCADE; CREATE SCHEMA public;
Often shows the error, because of missing owner permissions or other restrictions:
ERROR: must be owner of schema public SQL state: 42501