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

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

Leave a Comment

tech