Обсуждение: Quick question
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
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
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
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