1

I want to move all tables from schema xyz to the default schema public.

I can move individual tables with

ALTER TABLE table_name
    SET SCHEMA public;

And I can get all tables with

SELECT table_name FROM information_schema.tables WHERE table_schema='xyz'

Is there any way to combine these two together?

1

Use PL/pgSQL in a DO statement:

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;$$;
  • I am getting this Error Cannot move an owned sequence into another schema – Tachyons May 10 at 16:02
  • Oops, I forgot the relkind check. Fixed. – Laurenz Albe May 10 at 16:27
  • relation "table_name" does not exist – Tachyons May 10 at 17:09
  • Either it is a quote issue or because missing schema name as prepend. like xyz.table_name. Or both ? – Tachyons May 10 at 17:10
  • Can't be, I'd say. What is the failing SQL statement (real names please). It should not matter, but if you want the schema name included always, you can add SET search_path = pg_catalog; right after the BEGIN. – Laurenz Albe May 10 at 17:21
1

An improved version of Laurenz's Answer;

DO LANGUAGE plpgsql
$body$
DECLARE
   l_old_schema NAME = 'old_schema';
   l_new_schema NAME = 'new_schema';
   l_sql TEXT;
BEGIN
  FOR l_sql IN
    SELECT 
        format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE
        n.nspname = l_old_schema AND
        c.relkind = 'r'
  LOOP
    RAISE NOTICE 'appliying %', l_sql;
    EXECUTE l_sql;
  END LOOP;
END;
$body$;
0

Slightly modified version of Laurenz's Answer

DO 
$$ DECLARE
   table_record regclass;
BEGIN
  SET LOCAL search_path = xyz;
  FOR table_record IN
   SELECT  table_name  FROM information_schema.tables WHERE table_schema='xyz'
  LOOP
    EXECUTE format('ALTER TABLE %s SET SCHEMA public',table_record);
  END LOOP;
END; $$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.