Drop index postgres, what if duplicate index name exists

Question

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 ?

Laurenz Albe · Accepted Answer · 2022-03-11 11:57:11Z

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.

answered Mar 11, 2022 at 11:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jjanes · Accepted Answer · 2022-03-11 12:43:11Z

0

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)

answered Mar 11, 2022 at 12:43

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

Drop index postgres, what if duplicate index name exists

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related