I had a problem with restarting my code:

My code contains

alter table sch1.t1 set schema sch2;

I get an error

relation t1 already exists in schema sch2

I try to add if exists, implying that if t1 not in sch1 (already in sch2) it will be skip. But

it gave me the same problem.

Then I found a view with name t1. I drop the view and create with another name and my problem solved.

So, why PostgeSQL catch an error with alter table on view? How does it work? Why not only for table?

CodePudding user response：

Schemas are namespaces, and tables and views are some of the objects that can be created within them. The namespace is not sub-partitioned any further by object type, meaning it is an error to try to create a view and a table with the same name in the same schema.

CodePudding user response：

The message is saying, the destination table already exists! From psql, if you run \d sch2.t1 can see the table?

Try, dropping the destination table before the alter.