Re: remove indexes on a column?

"Vance Maverick" <vmaverick@pgp.com> 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