From: | "Vance Maverick" <vmaverick(at)pgp(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | remove indexes on a column? |
Date: | 2008-09-10 03:36:32 |
Message-ID: | DAA9CBC6D4A7584ABA0B6BEA7EC6FC0B01268CB1@hq-exch01.corp.pgp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-09-10 04:23:36 | Re: remove indexes on a column? |
Previous Message | Randal T. Rioux | 2008-09-10 02:48:29 | Re: 64-bit Compile Failure on Solaris 10 with OpenSSL |