"Zornoza Sanchez, Jose Blas" <jbzornoza@sia.es> writes:
> Hello, in this case both index and view have the same name (test), try a different one...
Yeah. If you try the example as-presented it fails immediately:
postgres=# create table foo (id int);
CREATE TABLE
postgres=# CREATE VIEW test AS SELECT * FROM foo;
CREATE VIEW
postgres=# CREATE INDEX test ON foo(id);
ERROR: relation "test" already exists
because you can't put a view named test and an index named test into
the same schema. (They share the namespace of tables.) What I think
the OP might have done is something similar to
postgres=# create schema s1;
CREATE SCHEMA
postgres=# create schema s2;
CREATE SCHEMA
postgres=# set search_path to s1, s2;
SET
postgres=# create table s2.foo (id int);
CREATE TABLE
postgres=# CREATE VIEW test AS SELECT * FROM foo;
CREATE VIEW
postgres=# CREATE INDEX test ON foo(id);
CREATE INDEX
postgres=# DROP INDEX test;
ERROR: "test" is not an index
HINT: Use DROP VIEW to remove a view.
View test is in schema s1, because that's the default creation schema
with this search_path setting. But index test is in s2, because
indexes are always put in the same schema as their parent table.
So the CREATE INDEX doesn't fail. But then the DROP searches the
search_path, and the first "test" it finds is the view s1.test,
so it complains.
regards, tom lane
