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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
