Re: Quick question

On Fri, 3 Oct 2003, Christopher Kings-Lynne wrote:

> Hi guys,
>
> If someone could help me with this, it would be cool.  How do I query
> the catalogs to find the underlying index for a constraint?  (Assuming
> the constraint is primary or unique)
>

For a primary key you can do:

SELECT cls.relname AS index_name
FROM pg_class cls, pg_constraint con, pg_index i
WHERE cls.oid = i.indexrelid AND con.conrelid = i.indrelid AND
i.indisprimary AND con.conname='<constraint name>';

This is not possible for a unique constraint because you can have multiple
unique constraints per table.  So you are left trying to match
pg_constraint.conkey to pg_index.indkey (for which no default operator
exists), but even this can fail if you have the unlikely situation of two
unique indexes covering the same columns.

Kris Jurka