How to change schema of multiple PostgreSQL tables in one operation?

Question

I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.

I know that I can use the following to move 1 table at a time.

ALTER TABLE [tablename] SET SCHEMA [new_schema]

Is it possible to move all of the tables to the new schema in one operation? If so, what would be the most efficient way to accomplish this task?

Rehabilitate

Answer 1

DO will do the trick:

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed
    LOOP
        EXECUTE format('ALTER TABLE public.%I SET SCHEMA [new_schema];', row.tablename);
    END LOOP;
END;
$$;

Donadonadee

That sounds exactly like what I was imagining. Since I'm new to postgres, where should I run this? I tried in pgAdmin 3 (windows) and psql command line, but got errors in both cases. Thanks for your assistance! – Rehabilitate

It's just a piece of SQL, run it just like any query in your favorite SQL-client. You could use psql, pgAdmin3 or something like that. – Donadonadee

The error i get from pgAdmin3 is: "ERROR: type "quote_ident" does not exist. SQL state: 42704. Context: PL/pgSQL function "inline_code_block" line 5 at EXECUTE statement." – Rehabilitate

Sorry, typo fixed. Please try again. – Donadonadee

The typo was the issue. Thanks so much for your help. It worked like a charm! – Rehabilitate

Excellent question and answer. – Router

You can move your functions as well with: DO$$ DECLARE row record; sql text = E'\n'; BEGIN FOR row IN select proname::text as proname, pg_get_function_identity_arguments(p.oid) AS params from pg_proc p JOIN pg_type t on p.prorettype = t.oid JOIN pg_namespace n on n.oid = p.pronamespace LEFT JOIN pg_description d on p.oid = d.objoid WHERE nspname = 'public' LOOP sql = CONCAT(sql, E'\n', 'ALTER FUNCTION public.', row.proname, '(', row.params, ') SET schema [new_schema];'); END LOOP; EXECUTE sql; END;$$; – Sampling

And you'll probably want the sequences, too: DO $$ DECLARE row record; BEGIN FOR row IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public' LOOP EXECUTE 'ALTER SEQUENCE public.' || quote_ident(row.sequence_name) || ' SET SCHEMA [new_schema];'; END LOOP; END; $$; – Estop

I read that quote_ident should be replaced with format(). Does this apply here, too? Source: stackoverflow.com/questions/31529945 – Lithium

@PaulfromC: Yes, I would use format() now – Donadonadee

Answer 2

-- ####### USING DBEAVER WHICH SUPPORT VARIABLES ########

-- @@@ ANSWER_1 -- USING DO @@@--------

-- Step1: Set variables one by one

@set _SCHEMA = 'public'
@set _COLUMN = 'dml_status'
@set _DATA_TYPE = 'integer'
@set _DEFAULT = '1'

-- Step2: Call the below procedure

DO
$$
DECLARE
row record;
query varchar;
BEGIN
FOR ROW IN SELECT table_name FROM information_schema.tables  WHERE table_schema = ${_SCHEMA}
LOOP
 query :='ALTER TABLE public.' || quote_ident(row.table_name) ||' ADD COLUMN IF NOT EXISTS '||${_COLUMN} || ' ' || ${_DATA_TYPE} ||' not null default ' || ${_DEFAULT} || ';' ;
execute query;
END LOOP;
END;
$$;


-- @@@ ANSWER_2 -- STORE PROCEDURE FN @@@--------


DROP FUNCTION addColumnToMultipleTables cascade;
create or replace function addColumnToMultipleTables()
returns void
LANGUAGE 'plpgsql'
as $$
DECLARE
row record;
query varchar;
BEGIN
FOR ROW IN SELECT table_name FROM information_schema.tables  WHERE table_schema = ${_SCHEMA}
LOOP
 query :='ALTER TABLE public.' || quote_ident(row.table_name) ||' ADD COLUMN IF NOT EXISTS '||${_COLUMN} || ' ' || ${_DATA_TYPE} ||' not null default ' || ${_DEFAULT} || ';' ;
 raise info 'query : % ', query;
execute query;
END LOOP;
END;
$$;

select addColumnToMultipleTables();

Plug

Answer 3

In psql prompt, The below query generates the list of ALTER TABLE statements at once, we can execute the list of statements to move tables from the "public" schema to "new_schema".

Update source schema(here: public) and target schema(here: new_schema) accordingly.

SELECT 'ALTER TABLE ' || quote_ident(schemaname) || '.' 
|| quote_ident(tablename) || ' SET SCHEMA new_schema;'
FROM pg_tables
WHERE schemaname = 'public';

Sample Output:

+--------------------------------------------------+
|                     ?column?                     |
+--------------------------------------------------+
| ALTER TABLE public.error SET SCHEMA new_schema;  |
| ALTER TABLE public.error1 SET SCHEMA new_schema; |
| ALTER TABLE public.error2 SET SCHEMA new_schema; |
| ALTER TABLE public.test SET SCHEMA new_schema;   |
+--------------------------------------------------+

Rugged