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

migrate, postgresql, postgresql-9.1, schema

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

Problem

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?

Original source

Related problems