/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

4 votes256 views1 answer
Oracle select first 10 rows
SQL
0 vote296 views1 answer
How to check rollback status in oracle
SQL
2 votes250 views1 answer
Query to find table size in oracle 12c
SQL
0 vote250 views1 answer
Oracle sql get value from several rows and concatenate strings
SQL
1 vote260 views2 answers
Drop function in sql
SQL
1 vote271 views2 answers
Sql server find all foreign keys that reference a column
SQL
0 vote248 views1 answer
Sqlalchemy query return only n results
SQL
4 votes276 views2 answers
Oracle unlock account
SQL
1 vote258 views1 answer
Get all db sizes in mysql server
SQL
0 vote268 views1 answer
How to set sql_mode for a query in CI model
SQL