/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

2 votes144 views1 answer
Set boolean flasksql
SQL
2 votes169 views2 answers
Calculate percentage in sql
SQL
0 vote141 views1 answer
SQLite3::SQLException: table "categories" already exists: CREATE TABLE "categories" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL
SQL
-1 vote97 views2 answers
Datetime sql server week
SQL
0 vote169 views2 answers
Create-toys-table-with-toy_name-column
SQL
0 vote161 views1 answer
Sql server synonym
SQL
2 votes144 views1 answer
SELECT INTO
SQL
1 vote169 views3 answers
Postgresql crete view
SQL
0 vote101 views2 answers
Denormalization in sql
SQL
3 votes166 views2 answers
Sqlite show table definition
SQL