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

In response to

• Quick question at 2003-10-03 02:18:48 from Christopher Kings-Lynne

pgsql-hackers by date

Next:	From: Michael Meskes	Date: 2003-10-03 10:59:19
	Subject: Re: ecpg doesn't compile (datetime.h/dtime_t)
Previous:	From: Hannu Krosing	Date: 2003-10-03 06:34:30
	Subject: Re: Index/Function organized table layout