Re: remove indexes on a column?

Vance Maverick <vmaverick@pgp.com> writes:
> Perfect!  Looks like I can get the names of the existing indexes by
> doing

> SELECT dep.relname
>  FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep
>  WHERE tab.relname = 'mytable'
>   AND col.attname = 'mycolumn'
>   AND col.attrelid = tab.oid
>   AND pd.refobjid = tab.oid
>   AND pd.refobjsubid = col.attnum
>   AND pd.objid = dep.oid
>   AND dep.relkind = 'i';

Too tired/lazy to check right now, but you should also look into
what the pg_depend representation is for constraints: I have a feeling
that a unique or primary key constraint yields a pg_depend structure
with an indirect linkage through a pg_constraint entry.

Also, the above query doesn't seem very schema-safe: what if there
are multiple tables named mytable?  Personally I'd try something
like tab.oid = 'mytable'::regclass instead of the relname test.

            regards, tom lane