Finding about an existing index 1

[kthakore]

Hello,
Can anybody tell me how I can find out if an index exists on a particular column of a table using pl/pgsql?

I am looking for the sql statement(s).

Thanks
Kushal

 

[ceco]

try

select * from pg_indexes where tablename = 'r_test';

where r_test is the table you are looking for

 

[kthakore]

ceco,
thank you for the response but I was looking to find if an index exists on a particular column of a table..

Thanks,
Kushal

 

[ceco]

well you can put some like or regexpression operator on the last column - indexdef and see if the column you are looking is there or if you know what is the name of the index you can look in indexname

other way is

select c1.relname, c2.relname, i.indkey from pg_class as c1, pg_class as c2, pg_index as i where i.indexrelid = c1.oid and i.indrelid = c2.oid and c2.relname = 'r_test';

now again r_test is the table name (c1 relation is not needed in the query, i've put it for clearity about which index it is) in i.indkey you will have int2vector containing all the columns in the index, but as numbers
create table r_test (id int4, int_f int4);
id will be 1
int_f - 2

you can use pg_attribute to get the names of the fields

well I am a little curious about what exactly you are trying to do, because ....

but if it still is not exactly the thing you need, here

http://www.postgresql.org/docs/7.4/interactive/catalogs.html

is info about all the system catalogues and the thing should be here ;-) also of course ask here

 

[kthakore]

Ceco,
I have a column name and a table name with me. What I want to do is see if there is an index on that column of that table. If there is an index on it, than drop it and re-create it with a new name. I do have the system catalogs of pg_class, pg_attribute and pg_indexes in front of me right now and am trying to figure it out. This is what i have so far but I am not there completely:
select obj.relname from pg_class obj, pg_attribute att
where att.attname='expiration'
and obj.relfilenode = att.attrelid
and obj.relkind = 'i'
I have not been able to tie the column 'expiration' with the specific table (that column name may exist in multiple tables)

 

[ceco]

well the isiest way to me is

select indexname from pg_indexes where tablename = 'r_test' and indexdef ~ '\\(expiration\\)';

if there is no pg_indexes table in your version

select c1.relname from pg_class as c1, pg_index as i where i.indexrelid = c1.oid and i.indrelid = 'r_test'::regclass and pg_get_indexdef(indexrelid) ~ '\\(expiration\\)';

or even

select indexrelid::regclass from pg_index where indrelid = 'r_test'::regclass and pg_get_indexdef(indexrelid) ~ '\\(expiration\\)';

the both queries return only the indexname you can then do drop index ...

this will search for index only by that column, if the index is multi column it will not find it, to do it you should a little tweak the regexpression

again the table is r_test and the column this time is expiration ;-)

 

[kthakore]

Thanks a lot Ceco.. that last post was very helpful.. it was bang-on!

Kushal