Re: unique indexes

Here is the query from phpPgAdmin that does what you are asking for:

SELECT
ic.relname AS index_name,
bc.relname AS tab_name,
a.attname AS column_name,
i.indisunique AS unique_key,
i.indisprimary AS primary_key
FROM
pg_class bc,
pg_class ic,
pg_index i,
pg_attribute a
WHERE
bc.oid = i.indrelid
and ic.oid = i.indexrelid
and a.attrelid = bc.oid
and bc.relname = '$table'
and
(
i.indkey[0] = a.attnum
or
i.indkey[1] = a.attnum
or
i.indkey[2] = a.attnum
or
i.indkey[3] = a.attnum
or
i.indkey[4] = a.attnum
or
i.indkey[5] = a.attnum
or
i.indkey[6] = a.attnum
or
i.indkey[7] = a.attnum
)
ORDER BY
index_name, tab_name, column_name;

This was adapted from the psql source. Hope it's what you need.

-Dan Wilson

----- Original Message -----
From: "Jason Davies" <jason_ddavies(at)yahoo(dot)com>
To: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Sunday, November 19, 2000 8:42 AM
Subject: [GENERAL] unique indexes

> Hi,
>
> Thankyou for your help with pg_trigger :)
>
> I am trying to list the indexes for a table. So far I've come up with this
SQL
> query:
>
> SELECT bc.relname AS TABLE_NAME,
> a.attname AS COLUMN_NAME,
> a.attnum as KEY_SEQ,
> ic.relname as PK_NAME
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> WHERE bc.relkind = 'r'
> and upper(bc.relname) = upper('tablename')
> and i.indrelid = bc.oid
> and i.indexrelid = ic.oid
> and ic.oid = a.attrelid
> ORDER BY table_name, pk_name, key_seq;
>
> I need to extend it slightly to get whether each key is unique or not. Any
> ideas on how this might be done?
>
> I would be grateful for any help.
> Thanks,
> Jason Davies.
>
> =====
> Jason Davies,
>
> _ _ _|_ _ _ _ _| _ | www.netspade.com
> | |(/_ | _\|_)(_|(_|(/_ | programming tutorials
> | | programming community
> ----------------------- | programming news
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Calendar - Get organized for the holidays!
> http://calendar.yahoo.com/