Re: Move all elements toward another schema?

On Tue, Feb 28, 2023 at 9:37 AM celati Laurent <laurent(dot)celati(at)gmail(dot)com>
wrote:

> Good afternoon,
>
> 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.

David J.