From: Christoph Haller <ch@rodos.fzk.de>
To: lutzeb@aeccom.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How to find out if an index is unique?
Date: Thu, 18 Jul 2002 16:22:00 METDST
Message-ID: <200207181422.QAA13952@rodos> (raw)
In-Reply-To: <15669.16833.74916.773006@cayambe.core.aeccom.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, lutzeb@aeccom.com
Subject: Re: How to find out if an index is unique?
In-Reply-To: <200207181422.QAA13952@rodos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox