/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 vote207 views1 answer
Mysql DDL
SQL
36 votes317 views6 answers
Change column data type sql
SQL
0 vote199 views1 answer
Trigger value from maltiple table to single table mysql
SQL
1 vote207 views1 answer
Alter add syntax
SQL
0 vote196 views1 answer
PDOException: SQLSTATE[HY093]: Invalid parameter numb
SQL
16 votes202 views1 answer
Mysql reset auto increment value
SQL
0 vote225 views1 answer
How to close external sql connection in laravel
SQL
18 votes205 views4 answers
Uninstall mysql ubuntu 18.04
SQL
0 vote272 views1 answer
Sql select first and last record of each group
SQL
1 vote221 views2 answers
Oracle leftmost characters
SQL