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)

Chris



Fri, 03 Oct 2003 10:18:48 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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


Fri, 3 Oct 2003 06:22:56 -0400 (EDT)
Kris Jurka <books@ejurka.com>
Re: Quick question
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 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)

A first approximation is that the constraint and the index have the same
name, but I suppose someone could break that association by renaming the
index.  Look in pg_depend for an internal dependency entry from the
index to the constraint if you want to be sure.

			regards, tom lane

Fri, 03 Oct 2003 09:59:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Quick question 
Thanks - that's what I ended up doing.  Hooray for CLUSTER indexes and 
constraints in phpPgAdmin CVS now!

Chris

Tom Lane wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 
>>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)
> 
> 
> A first approximation is that the constraint and the index have the same
> name, but I suppose someone could break that association by renaming the
> index.  Look in pg_depend for an internal dependency entry from the
> index to the constraint if you want to be sure.
> 
> 			regards, tom lane


Sat, 04 Oct 2003 17:35:18 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Quick question