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

Question

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?

Answer 1

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;$$;

Answer 2

An improved version of Laurenz's Answer;

DO LANGUAGE plpgsql
$body$
DECLARE
   l_old_schema NAME = 'old_schema';
   l_new_schema NAME = 'new_schema';
   l_sql TEXT;
BEGIN
  FOR l_sql IN
    SELECT 
        format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE
        n.nspname = l_old_schema AND
        c.relkind = 'r'
  LOOP
    RAISE NOTICE 'appliying %', l_sql;
    EXECUTE l_sql;
  END LOOP;
END;
$body$;

Answer 3

Slightly modified version of Laurenz's Answer

DO 
$$ DECLARE
   table_record regclass;
BEGIN
  SET LOCAL search_path = xyz;
  FOR table_record IN
   SELECT  table_name  FROM information_schema.tables WHERE table_schema='xyz'
  LOOP
    EXECUTE format('ALTER TABLE %s SET SCHEMA public',table_record);
  END LOOP;
END; $$

Answer 4

--move all tables from schema 'b' to schema 'a':

1. select 'ALTER TABLE ' || table_name || ' SET SCHEMA b' from information_schema.tables where table_schema = 'a';

2. run all altered statements

set search_path = 'a'; -- all alter statements

---

move all functions from schema 'a' to 'b'

1. select 'ALTER FUNCTION ' || routine_name || ' SET SCHEMA b' from information_schema.routines where routine_type = 'FUNCTION' and specific_schema = 'a'

set search_path a; -- alter statements

Answer 5

Alternatively, you could just do a

alter database pgdb_name set search_path to mysql_dbname, public, $user;

as described here