Drop index postgres, what if duplicate index name exists-CodePudding

below query works perfectly in postgre, because duplicate index within table is not allowed but with in DB it is allowed.

sandbox=# create schema test;
CREATE SCHEMA
sandbox=# create table public.a (a_id integer not null);
CREATE TABLE
sandbox=# create table test.a (a_id integer not null);
CREATE TABLE
sandbox=# create index a_idx on public.a (a_id);
CREATE INDEX
sandbox=# create index a_idx on test.a (a_id);
CREATE INDEX

what happens when I do

DROP INDEX a_idx;

will both the indexes get deleted ?
can I write DROP INDEX test.a.a_idx ?
how the index look up works while deleting ?

CodePudding user response：

What happens depends on the setting of search_path. PostgreSQL searches the existing schemas on search_path in turn, and as soon as it finds an index of that name, it drops the index and is done.

CodePudding user response：

can I write DROP INDEX test.a.a_idx ?

The index is in the same schema as its table, so it would just be DROP INDEX test.a_idx (if you want/need to override search_path)