Re: [GENERAL] unique fields

Mirko Kaffka wrote:
>
> Hi all,
>
> How can I find out which fields of an existing table have been created as
> unique fields? Which system tables can I check?
>
> Thanks Mirko
> --
> while (!asleep()) sheep++;


try following sql-statment :




SELECT bc.relname AS tab_name,
       ic.relname AS index_name,
       i.indisunique,
       a.attname
  FROM pg_class bc,             -- tab class
       pg_class ic,             -- index class
       pg_index i,
       pg_attribute a           -- att in base
  WHERE i.indrelid = bc.oid
     and i.indexrelid = ic.oid
     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
        )
     and a.attrelid = bc.oid
     and i.indproc = '0'::oid   -- no functional indices
     and i.indisunique = 't'
     and bc.relname !~* '^pg_'
  ORDER BY tab_name, index_name, attname;
\p\q

if you replace
    and bc.relname !~* '^pg_'
by

    and bc.relname = '<your table>'

you will get only the indices of <your table>

--
MfG

-------------------------------------------------------------------------
- Karin Probost
- Bergische Universitaet Gesamthochschule Wuppertal
- RECHENZENTRUM  Raum P-.09.05
- Gaussstr. 20
- D-42097 Wuppertal
- Germany
-
- Tel. : +49 -202 /439 2809 ,Fax -2910
--Email: mailto:probost@rz.uni-wuppertal.de
--Home : http://www.hrz.uni-wuppertal.de/hrz/personen/k_probost.html
-------------------------------------------------------------------------