From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200207181422.QAA13952@rodos>
Subject: Re: How to find out if an index is unique?
To: lutzeb@aeccom.com (Dirk Lutzebaeck)
Date: Thu, 18 Jul 2002 16:22:00 METDST
Cc: pgsql-sql@postgresql.org
In-Reply-To: <15669.16833.74916.773006@cayambe.core.aeccom.com>;
	from "Dirk Lutzebaeck" at Jul 17, 102 12:07 (noon)

Dirk, 

May be you can use this 

create view sesql_userindexes as
SELECT upper(c.relname) AS TBL_NAME, upper(i.relname) AS IDX_NAME,
CASE WHEN x.indisunique=false THEN 0 ELSE 1 END AS UNIQUE_FLAG, 1+
(CASE WHEN x.indkey[1]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[2]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[3]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[4]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[5]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[6]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[7]=0 THEN 0 ELSE 1 END) AS IDXCOL_TOTAL, x.indkey AS COL_SEQ
FROM pg_index x, pg_class c, pg_class i
WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid))
and not (c.relname ~* 'pg_') ;

select * from sesql_userindexes order by tbl_name, idx_name ; 

gives you detailed information about all user-defined indices. 

Regards, Christoph 

> 
> is there a way to ask the system tables if a given index was created
> with the unique qualification? I don't want to insert data to try.
>