/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

7 votes367 views2 answers
Postgres concat
SQL
10 votes349 views2 answers
Show tables in schema oracle
SQL
3 votes359 views3 answers
C# sql select
SQL
2 votes385 views2 answers
Spring import sql
SQL
1 vote336 views1 answer
Delete mysql from mac
SQL
2 votes350 views1 answer
Get hour from date sql
SQL
0 vote356 views1 answer
Oracle sql count occurrences of value in column
SQL
4 votes327 views1 answer
Update table from another table oracle
SQL
0 vote340 views2 answers
Database owner can't create new tables postgres
SQL
48 votes415 views3 answers
Psql create database
SQL