/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

11 votes140 views2 answers
Having clause
SQL
24 votes155 views4 answers
Postgres delete database
SQL
4 votes143 views1 answer
Postgresql show current database
SQL
3 votes97 views2 answers
Mysql sum with condition
SQL
0 vote141 views1 answer
Sql beginning of previous month
SQL
0 vote202 views1 answer
Parent and child link in mysql
SQL
18 votes152 views4 answers
Uninstall mysql on ubuntu
SQL
11 votes154 views1 answer
How to get ddl for materialized view
SQL
0 vote147 views1 answer
Java mysql swing example
SQL
0 vote142 views1 answer
SQL column name Oracle
SQL