With postgresql 13, I want to find a way to move 100 tables from schema 'A' to schema 'B'. Not just data. But also indexes, primary keys, constraints (INCLUDING ALL).
As far as i know, this piece of code would move the data. But how to also move indexes, constraints, primary key?
DO $$ DECLARED row record; BEGIN FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'A' -- and other conditions, if needed LOOPS EXECUTE format('ALTER TABLE A.%I SET SCHEMA [B];', row.tablename); END LOOP; END; $$;
Run the code in some test environment and see exactly what it does instead of guessing.
In any case, at least for constraints it isn't like they have an existence beyond the table upon which they are defined, there is no CREATE/ALTER CONSTRAINT command; moving a table necessarily moves anything that is inherently a part of that table.
There is an ALTER INDEX command although since it lacks a "SCHEMA" instruction I would assume the indexes, which are indeed a fundamental part of the table, would be moved along with the table. Experiment if you want a more concrete answer.
But it doesn't make sense to have these things be in different schemas than the tables they modify so it all makes sense from a design standpoint.
