/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

3 votes169 views1 answer
Start mysql server linux terminal
SQL
58 votes555 views4 answers
Mysql drop column
SQL
3 votes190 views2 answers
Order by postgresql
SQL
1 vote192 views1 answer
Code: "ER_TRUNCATED_WRONG_VALUE", errno: 1292, sqlMessage: "Incorrect date value: '2021-03-20T18:30:00.000Z' for column 'mgfdate' at row 1", sqlState: "22007", index: 0, …}
SQL
8 votes173 views2 answers
Mysql get first x characters
SQL
4 votes182 views1 answer
Oracle select first 10 rows
SQL
1 vote213 views1 answer
Postgres power
SQL
6 votes175 views2 answers
Mysql set primary key
SQL
0 vote186 views2 answers
Close external port 3306 with iptables
SQL
1 vote307 views1 answer
Primary key reset in SQL database
SQL