/SQL/Change schema of all tables postgres

Change schema of all tables postgres

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

Source: dba.stackexchange.com

Similar codes

0 vote284 views1 answer
Storing RGBA in mysql db
SQL
11 votes309 views6 answers
Distinct sql
SQL
0 vote283 views1 answer
Oracle drop default value
SQL
-1 vote317 views1 answer
Minus vs except in sql
SQL
2 votes320 views1 answer
Coalesce sql
SQL
7 votes310 views2 answers
Mysql Public Key Retrieval is not allowed
SQL
0 vote298 views1 answer
Mysql where in keep order
SQL
1 vote292 views2 answers
Sql multiline comment
SQL
3 votes286 views1 answer
Oracle user quota on tablespace
SQL
2 votes303 views2 answers
Mysql alter column default
SQL