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(at)rz(dot)uni-wuppertal(dot)de
--Home : http://www.hrz.uni-wuppertal.de/hrz/personen/k_probost.html
-------------------------------------------------------------------------