/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

2 votes123 views1 answer
Can you use a where clause for a child query
SQL
50 votes205 views4 answers
Sql alter type of column
SQL
0 vote144 views1 answer
Mysql page segment
SQL
6 votes139 views1 answer
Oracle desc table primary key
SQL
3 votes149 views6 answers
DB::table('users')->get();
SQL
0 vote155 views1 answer
Check command
SQL
1 vote143 views1 answer
Convert varchar to int in sqlite
SQL
0 vote177 views1 answer
Grouping by email domain mysql
SQL
1 vote139 views1 answer
Random name function in mysql for nvarchar
SQL
0 vote104 views2 answers
Sql update from two different database
SQL