Re: How to find out if an index is unique? - Mailing list pgsql-sql

From Christoph Haller
Subject Re: How to find out if an index is unique?
Date
Msg-id 200207181422.QAA13952@rodos
Whole thread Raw
In response to How to find out if an index is unique?  (Dirk Lutzebaeck <lutzeb@aeccom.com>)
List pgsql-sql
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.
> 



pgsql-sql by date:

Previous
From: "Ligia Pimentel"
Date:
Subject: Re: How to find out if an index is unique?
Next
From: Robert Treat
Date:
Subject: Re: how do i import my sql query result to a file