/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 vote109 views1 answer
Drop constraint in ms sql
SQL
7 votes111 views3 answers
Sql fill table with info from anothe rtable
SQL
1 vote118 views1 answer
Oracle create index if not exists
SQL
3 votes103 views1 answer
Oracle revoke grant
SQL
6 votes105 views3 answers
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.term.year' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SQL
0 vote103 views2 answers
How to check if sqlite table exists in nodejs
SQL
2 votes114 views2 answers
Convert_tz mysql
SQL
0 vote116 views1 answer
Oracle calculate statistics on partition
SQL
0 vote229 views2 answers
Create new table from existing table with data in sql server
SQL
1 vote112 views4 answers
Aggregate function in sql
SQL