'Drop index postgres, what if duplicate index name exists

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;
  1. will both the indexes get deleted ?
  2. can I write DROP INDEX test.a.a_idx ?
  3. how the index look up works while deleting ?
postgresqlindexing


Solution 1:[1]

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.

Solution 2:[2]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Laurenz Albe
Solution 2 jjanes

Related Questions