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?

score 64 · Accepted Answer · edited Oct 05 '22 at 05:52

64

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

edited Oct 05 '22 at 05:52

Paul Smith

299
2
13

answered Apr 19 '12 at 08:29

Frank Heikens

117,544
24
142
135

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! – RyanKDalton Apr 19 '12 at 17:03
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. – Frank Heikens Apr 19 '12 at 17:11
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." – RyanKDalton Apr 19 '12 at 17:12
Sorry, typo fixed. Please try again. – Frank Heikens Apr 19 '12 at 17:14
The typo was the issue. Thanks so much for your help. It worked like a charm! – RyanKDalton Apr 19 '12 at 17:17
Excellent question and answer. – Shane Dec 09 '12 at 00:04
2

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;$$;` – Michael M Oct 02 '13 at 22:29
5

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; $$; – Catherine Devlin Sep 20 '16 at 14:47
I read that `quote_ident` should be replaced with `format()`. Does this apply here, too? Source: https://stackoverflow.com/questions/31529945/ – Paul Smith Oct 04 '22 at 20:40
1

@PaulfromC: Yes, I would use format() now – Frank Heikens Oct 04 '22 at 20:53

Mohsin Ejaz · Answer 2 · 2022-07-22T06:35:41.617

-- ####### 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();

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

2 Answers2

Linked