CODEWITHSUNDEEP
postgresqlalter-tablepostgresql-11
yuoggy
yuoggy

Reputation: 147

How actually work alter table in postgresql?

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?

Upvotes: 0

Views: 288

Answers (2)

Z4-tier
Z4-tier

Reputation: 7998

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.

Upvotes: 1

Frank N Stein
Frank N Stein

Reputation: 2518

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.

Upvotes: 0

Related Questions