How do I move all tables from one Postgres schema to another
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;$$;
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; $$