loose keys after alter table set schema

Question

ran into a problem on PostgreSQL 12.5

ALTER TABLE old_shcema.my_table SET new_schema

first - everything is OK, everything is transferred except triggers, but if you try back

ALTER TABLE new_schema.my_table SET old_schema

then keys, foreign keys will be lost

what is the reason? Am I doing something wrong or is this a postgres bug?

Answer 1

Doesn't do it here (PostgreSQL v14).

richardh=> CREATE SCHEMA a;
CREATE SCHEMA
richardh=> CREATE SCHEMA b;
CREATE SCHEMA
richardh=> CREATE TABLE a.t1 (id int PRIMARY KEY);
CREATE TABLE
richardh=> CREATE TABLE a.t2 (idref int NOT NULL REFERENCES a.t1);
CREATE TABLE
richardh=> ALTER TABLE a.t1 SET SCHEMA b;
ALTER TABLE
richardh=> \d a.t2
                   Table "a.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 idref  | integer |           | not null | 
Foreign-key constraints:
    "t2_idref_fkey" FOREIGN KEY (idref) REFERENCES b.t1(id)

richardh=> ALTER TABLE a.t1 SET SCHEMA a;
ERROR:  relation "a.t1" does not exist
richardh=> ALTER TABLE b.t1 SET SCHEMA a;
ALTER TABLE
richardh=> \d a.t2
                   Table "a.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 idref  | integer |           | not null | 
Foreign-key constraints:
    "t2_idref_fkey" FOREIGN KEY (idref) REFERENCES a.t1(id)