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. (I know what indexes are *supposed* to be there, but depending on the migration history of the specific instance, the names may vary.)
I tried writing this logic using the system catalogs (pg_index, etc.), and it works up to a point. But when some of the indexes involve expressions, e.g.
CREATE INDEX foo_lower_value ON foo(lower(value));
it's not so easy to do the lookup. In this case, the column index is coded deep in an expression string ("in nodeToString() representation"), and I don't see how to parse that.
Alternatively, I could take the brute-force approach:
- create a new column with the same type
- copy the values from the old column to the new
- drop the old column, presumably killing all the indices
- rename the new column to the old name
But that involves a lot of data copying, table restructuring, etc.
Is there a good way to do this? Thanks,
Vance
