pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to find out if an index is unique?
5+ messages / 5 participants
[nested] [flat]
* How to find out if an index is unique?
@ 2002-07-17 10:06 Dirk Lutzebaeck <lutzeb@aeccom.com>
1970-01-01 00:00 ` Re: How to find out if an index is unique? Christoph Haller <ch@rodos.fzk.de>
2002-07-17 10:15 ` Re: How to find out if an index is unique? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-17 15:10 ` Re: How to find out if an index is unique? Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-17 22:27 ` Re: How to find out if an index is unique? Ligia Pimentel <pimentel_ligia@hotmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Dirk Lutzebaeck @ 2002-07-17 10:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out if an index is unique?
2002-07-17 10:06 How to find out if an index is unique? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
3 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: lutzeb@aeccom.com; +Cc: 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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out if an index is unique?
2002-07-17 10:06 How to find out if an index is unique? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2002-07-17 10:15 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
3 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2002-07-17 10:15 UTC (permalink / raw)
To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out if an index is unique?
2002-07-17 10:06 How to find out if an index is unique? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2002-07-17 15:10 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
3 siblings, 0 replies; 5+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-17 15:10 UTC (permalink / raw)
To: Dirk Lutzebaeck <lutzeb@aeccom.com>; pgsql-sql
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_bank USING 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find out if an index is unique?
2002-07-17 10:06 How to find out if an index is unique? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2002-07-17 22:27 ` Ligia Pimentel <pimentel_ligia@hotmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Ligia Pimentel @ 2002-07-17 22:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-07-17 22:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-17 10:06 How to find out if an index is unique? Dirk Lutzebaeck <lutzeb@aeccom.com>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2002-07-17 10:15 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-17 15:10 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-17 22:27 ` Ligia Pimentel <pimentel_ligia@hotmail.com>
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