Skip site navigation (1) Skip section navigation (2)

Re: Quick question

From: Kris Jurka <books(at)ejurka(dot)com>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Quick question
Date: 2003-10-03 10:22:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group