How to move many tables from one schema into another schema in PostgreSQL automatically?

user_7228071 0
Viewed 685

I want to move all tables from one schema into another schema. I do not want to do it manually. How can I do it automatically. Both schemas are in one database.

postgresql schema
2 Answers

According to the documentation you can set a new schema new_schema of a given table my_table with:

alter table my_table set schema new_schema;

To set schema of all tables in a schema (old_schema here) you should use dynamic SQL in a plpgsql block:

do $$
declare 
    rec record;
begin
    for rec in
        select relname
        from pg_class
        where relkind = 'r'
        and relnamespace = 'old_schema'::regnamespace
        -- and relname ilike '%' -- you can filter table names here
    loop
        execute format(
            'alter table old_schema.%I set schema new_schema',
            rec.relname);
    end loop;
end; $$

Read also about pg_class in the docs.

user_19957380

I found another solution before @klin writes his answer. I share that solution with you:

DO
$$DECLARE
   my_table record;
BEGIN
  
   FOR my_table IN
      SELECT table_name FROM information_schema.tables WHERE table_schema='my_schema'

   LOOP
      EXECUTE format(' CREATE TABLE my_target_schema.%s AS ( SELECT *  FROM  my_schema.%s) ',     my_table.table_name,my_table.table_name);
   END LOOP;
END;$$;

It worked for me. n fact, this solution just copies the tables in the target schema but still you should delete them from the first schema if thats what you want.

user_72280710
Related