remove indexes on a column?

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

Responses

Browse pgsql-general by date

  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