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@yahoo.com>
To: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Cc: <pgsql-general@postgresql.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/