How to remove Unique Indexes without knowing its name

user_2770024 0
Viewed 56

I am using PostgreSQL and I need to remove an index without knowing its name.

I have multiple instances where the index was created using Liquibase script. I want to drop the indexes created and add a new index.

I have a script that would give me the Drop statements.

select format('drop index %I.%I;', schemaname, indexname) as qry
from pg_indexes
where schemaname not in ('pg_catalog', 'pg_toast') 
and tablename='table_name' 
and indexname!='table_name_pkey'

But I am not sure how to use the result set to drop the inedexes. Executing using SQL Shell/ Liquibase where I can add SQL Statements. Cant make long functions to do that.

postgresql
1 Answers

Try this with DO block like below:

do $$
declare
x text;
begin

x=(select string_agg(format('drop index %I.%I;', schemaname, indexname), ' ') as qry
from pg_indexes
where schemaname not in ('pg_catalog', 'pg_toast') 
and tablename='test' 
and indexname!='test_pkey');

execute x;
end $$

DEMO1

Same you can do with function/Procedure(Depend upon your Version of PostgreSQL) like below:

create or replace function remove_index(table_name varchar, table_pkey varchar) returns void
as $$
declare
x text;
begin

x=(select string_agg(format('drop index %I.%I;', schemaname, indexname), ' ') as qry
from pg_indexes
where schemaname not in ('pg_catalog', 'pg_toast') 
and tablename=table_name
and indexname!=table_pkey);

execute x;

end;

$$
language plpgsql

and call above function like below:

select * from remove_index('test','test_pkey')

DEMO2

user_77724830
Related