10

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?

5 Answers 5

9

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;$$;
8
  • I am getting this Error Cannot move an owned sequence into another schema
    – Tachyons
    Commented May 10, 2019 at 16:02
  • Oops, I forgot the relkind check. Fixed. Commented May 10, 2019 at 16:27
  • relation "table_name" does not exist
    – Tachyons
    Commented May 10, 2019 at 17:09
  • Either it is a quote issue or because missing schema name as prepend. like xyz.table_name. Or both ?
    – Tachyons
    Commented May 10, 2019 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. Commented May 10, 2019 at 17:21
5

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$;
2

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; $$
1

--move all tables from schema 'b' to schema 'a':

  1. select 'ALTER TABLE ' || table_name || ' SET SCHEMA b' from information_schema.tables where table_schema = 'a';

  2. run all altered statements

set search_path = 'a'; -- all alter statements


move all functions from schema 'a' to 'b'

  1. select 'ALTER FUNCTION ' || routine_name || ' SET SCHEMA b' from information_schema.routines where routine_type = 'FUNCTION' and specific_schema = 'a'

set search_path a; -- alter statements

-1

Alternatively, you could just do a

alter database pgdb_name set search_path to mysql_dbname, public, $user;

as described here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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