postgresqlschemamigratepostgresql-9.1

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

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?

Solution

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