Thread: How to find out if an index is unique?

Hello,

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.

Greetings,

Dirk

On Wed, 17 Jul 2002, Dirk Lutzebaeck wrote:

Yes.

Select the oid from pg_class where relname is your index name.
Then search in pg_index for column "indisunique" using the previous oid
as "indexrelid".

> 
> Hello,
> 
> 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.
> 
> Greetings,
> 
> Dirk
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

perhaps This is more elegant has Tom has indicated :

select pg_get_indexdef(oid) from pg_class where relname = 'email_bank_email';
pg_get_indexdef
-------------------------------------------------------------------------------CREATE UNIQUE INDEX email_bank_email ON
email_bankUSING btree (lower(email)) 
(1 row)


substitue  'email_bank_email' with name of your index.....


regds
mallah.




On Wednesday 17 July 2002 15:36, Dirk Lutzebaeck wrote:
> Hello,
>
> 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.
>
> Greetings,
>
> Dirk
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

You could also use describe (in psql environment )

psql mydatabase
mydatabase=# \d indexname

Index "indexname"Attribute  |     Type
------------+---------------fieldname    | datatype
unique btree

The word "unique" will show up only if the index has the unique
qualification, otherwise it will read only "btree".

Hope this helps.

Ligia


"Dirk Lutzebaeck" <lutzeb@aeccom.com> wrote in message
news:15669.16833.74916.773006@cayambe.core.aeccom.com...
>
> Hello,
>
> 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.
>
> Greetings,
>
> Dirk
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

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.
>