Move all elements toward another schema?

On Tue, Feb 28, 2023 at 9:37 AM celati Laurent <laurent.celati@gmail.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.

As far as i know, this piece of code would move the data. But how to also
move indexes, constraints, primary key?

create schema if not exists a;

create schema if not exists b;
create table a.a(id integer not null constraint pk_a primary key, name
text);
create index idx_a_name on a.a(name);
alter table a.a set schema b;

select relnamespace::regnamespace, relname from pg_class where relname in
('a','pk_a','idx_a_name');
relnamespace | relname
--------------+------------
b | a
b | idx_a_name
b | pk_a
(3 rows)