Thread: query system tables to find columns unique constraint is constraining?

Hi-

I have been fiddling around trying to figure out which columns some unique
constraints are constraining by querying  the system tables.....with zero
luck.

Can anyone suggest how to do this?

thank!
Beth

"Beth Gatewood" <beth@vizxlabs.com> writes:
> I have been fiddling around trying to figure out which columns some unique
> constraints are constraining by querying  the system tables.....with zero
> luck.

Look at the pg_index row for the index that's implementing the
constraint.

Rather than messing with the pg_index entry directly, you might want to
use pg_get_indexdef(), which is a helper function for pg_dump:

regression=# create table foo (f1 int, constraint foo_f1 unique (f1));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'foo_f1' for table 'foo'
CREATE TABLE
regression=# select pg_get_indexdef(oid) from pg_class where relname = 'foo_f1';                 pg_get_indexdef
----------------------------------------------------CREATE UNIQUE INDEX foo_f1 ON foo USING btree (f1)
(1 row)

        regards, tom lane

It's all in the pg_index table.  Check for unique indices.

Chris

----- Original Message -----
From: "Beth Gatewood" <beth@vizxlabs.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, May 28, 2002 11:47 AM
Subject: [SQL] query system tables to find columns unique constraint is
constraining?


>
>
> Hi-
>
> I have been fiddling around trying to figure out which columns some unique
> constraints are constraining by querying  the system tables.....with zero
> luck.
>
> Can anyone suggest how to do this?
>
> thank!
> Beth
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>