snippetsqlMinor

How do I move all tables from one Postgres schema to another

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablespostgresallmoveoneanotherhowfromschema

Problem

I want to move all tables from schema xyz to the default schema public.

I can move individual tables with

ALTER TABLE table_name
    SET SCHEMA public;

And I can get all tables with

SELECT table_name FROM information_schema.tables WHERE table_schema='xyz'

Is there any way to combine these two together?

Solution

Use PL/pgSQL in a DO statement:

DO
$DECLARE
   p_table regclass;
BEGIN
   SET LOCAL search_path='xyz';
   FOR p_table IN
      SELECT oid FROM pg_class
      WHERE relnamespace = 'xyz'::regnamespace
        AND relkind = 'r'
   LOOP
      EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
   END LOOP;
END;$;

Code Snippets

DO
$$DECLARE
   p_table regclass;
BEGIN
   SET LOCAL search_path='xyz';
   FOR p_table IN
      SELECT oid FROM pg_class
      WHERE relnamespace = 'xyz'::regnamespace
        AND relkind = 'r'
   LOOP
      EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
   END LOOP;
END;$$;

Context

StackExchange Database Administrators Q#237813, answer score: 9

Revisions (0)

No revisions yet.