How to move tables from public to other schema in Postgres

Pre-migration Checkup

Ensuring Smooth Transition

Identify the interdependencies that might affect the move:

1. Indexes & Constraints: Verify their existence and recreate within the new schema as needed.
2. Referenced Sequences: Make sure sequences paired with primary keys remain valid after the move.
3. Trigger Functions: Use ALTER FUNCTION .. SET SCHEMA if moves impact trigger actions.

Moving Tables Right

Follow these steps for a smooth transition with no hiccup or coffee spill:

1. Create Empty Schema:

   CREATE SCHEMA IF NOT EXISTS target_schema;
   

2. Transfer Table: Migrate each table individually, all data included!

   ALTER TABLE public.example_table SET SCHEMA target_schema;  -- Moving. Please beware of tripping hazards.
   

3. Update Foreign Keys: Update any tables having foreign keys that reference the moved table.
4. Check Sequences: Check primary key sequences post-move to ensure sequence ownership remains intact.

Advanced Measures

• Syntax Compatibility: Ensure the syntax is compliant with your PostgreSQL version.
• Data Consistency: When moving populated tables, be cautious to avoid data corruption.
• Trigger Functions: Use ALTER FUNCTION .. SET SCHEMA for smoother trigger function relationships post-move.

Visualization

Imagine shifting tables in PostgreSQL like relocating from one apartment to another:

📚 Old Apartment (public): [Table1, Table2, Table3]
🔍 New Apartment (target_schema): [ ]

Here comes the moving truck:

🚚 Moving 'Table2' from 'public' to 'target_schema'

Finally, Home sweet home:

📚 public: [Table1, Table3]
📚 target_schema: [Table2]

All safe at their new address!

Automating your move

Scripting the Process

For a hands-free transfer, swing into action with a dynamic SQL script:

DO $$
DECLARE
    table_name text;
BEGIN
    FOR table_name IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
    LOOP
        -- Look ma, no hands!
        EXECUTE format('ALTER TABLE public.%I SET SCHEMA target_schema;', table_name);
    END LOOP;
END $$;


Guarding Against Pitfalls

• Broken Dependencies: pg_dump can help reveal dependencies, preventing post-migration breakages.
• Invalid Foreign Keys: Revalidate foreign keys post-migration to avoid "dangling" relations.
• Privilege Issues: Mind to check if the acting role has suitable access rights for such schema operations.