Resetting the `public` Schema in PostgreSQL

postgres-public-reset.md

Resetting the public Schema in PostgreSQL

It is possible to "reset" the public schema in a PostgreSQL database using SQL. This can be achieved in two main ways, depending on whether you want to drop the entire schema or just the tables within it.

---

Method 1: Dropping and Recreating the public Schema

This method removes all objects within the schema—tables, views, sequences, etc.—and then recreates it.

SQL Commands

DROP SCHEMA public CASCADE;  -- Drops the public schema and all contained objects

CREATE SCHEMA public;        -- Recreates the public schema

GRANT ALL ON SCHEMA public TO postgres;  -- Restore default permissions

GRANT ALL ON SCHEMA public TO public;

Explanation

• DROP SCHEMA public CASCADE;: Deletes the schema and all objects inside it.
• CREATE SCHEMA public;: Recreates a clean public schema.
• GRANT statements: Restore default access permissions.

✅ Use this method if you want a complete reset of the schema.

---

Method 2: Dropping Only Tables in the public Schema

This method retains non-table objects (e.g., views, functions) while removing only the tables.

SQL Script

DO $$ 
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

Explanation

• Iterates over all tables in the public schema.
• Dynamically drops each table using EXECUTE.
• CASCADE ensures dependent objects (e.g., foreign keys) are removed too.

✅ Use this method if you want to preserve non-table objects in the schema.

---

Key Considerations

• Backup Data: Both methods are destructive. Back up any needed data before proceeding.
• Permissions: Ensure permissions are correctly restored after recreating the schema.
• Dependencies: Use CASCADE cautiously to automatically handle dependencies.

---

When to Use Each Method

Goal	Recommended Method
Full schema reset	Method 1: Drop & Recreate Schema
Remove tables only	Method 2: Drop Only Tables

---

This gist was generated with the help of ChatGPT based on information provided and saved by the user.