Perfect! Looks like I can get the names of the existing indexes by doing SELECT dep.relname FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep WHERE tab.relname = 'mytable' AND col.attname = 'mycolumn' AND col.attrelid = tab.oid AND pd.refobjid = tab.oid AND pd.refobjsubid = col.attnum AND pd.objid = dep.oid AND dep.relkind = 'i'; Thanks. Vance On Wed, 2008-09-10 at 00:23 -0400, Tom Lane wrote: > "Vance Maverick" <vmaverick@xxxxxxx> writes: > > I'd like to write a SQL script, possibly with some PL/pgSQL, that can > > find all indexes on a column -- so I can remove them, and set up > > exactly the indexes I want. > > Yeah, this seems a bit tricky if you have expression indexes involving > the column. I concur that trying to parse the expressions is a bad > idea --- even if your code works today, it'll probably break in future > PG releases, because the nodetree representation is not very stable. > > What I'd look for is pg_depend entries showing indexes that depend on > the column. Here's a hint: > > regression=# create table foo (f1 int); > CREATE TABLE > regression=# create index fooi on foo (abs(f1)); > CREATE INDEX > regression=# select * from pg_depend where refobjid = 'foo'::regclass; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > ---------+--------+----------+------------+----------+-------------+--------- > 1247 | 534605 | 0 | 1259 | 534603 | 0 | i > 1259 | 534606 | 0 | 1259 | 534603 | 1 | a > (2 rows) > > regression=# select 534606::regclass; > regclass > ---------- > fooi > (1 row) > > > regards, tom lane