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

postgresql schema migrate postgresql-9.1
RyanKDalton picture RyanKDalton · Apr 19, 2012 · Viewed 18.1k times · Source

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?

Answer

Frank Heikens picture Frank Heikens · Apr 19, 2012

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 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';
    END LOOP;
END;
$$;

Related questions

How can I export the schema of a database in PostgreSQL?

My computer broke down but fortunately I backed up the folder C:\Program Files\PostgreSQL. Now I'm working in a new computer and I would like to import the previous Postgres databases that are stored in the external disk. I …

Read More
What GRANT USAGE ON SCHEMA exactly do?

I'm trying to create for the first time a Postgres database, so this is probably a stupid question. I assigned basic read-only permissions to the db role that must access the database from my php scripts, and I have a …

Read More
PostgreSQL: Create schema in specific database

I need to write an sql script that creates both a new database AND a new schema in the database I just created. How can I do it? Can I somehow change the current database to the new one? Or …

Read More