Re: unique indexes

Tom,

Thanks for the update on this query. I'm not positive where I found this
query, but I'm pretty sure it was for a v6.5x something. Anyway, thanks.
phpPgAdmin has been updated.

-Dan

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dan Wilson" <phpPgAdmin(at)acucore(dot)com>
Cc: <jason(at)netspade(dot)com>; <pgsql-general(at)postgresql(dot)org>
Sent: Sunday, November 19, 2000 10:14 AM
Subject: Re: [GENERAL] unique indexes

> "Dan Wilson" <phpPgAdmin(at)acucore(dot)com> writes:
> > Here is the query from phpPgAdmin that does what you are asking for:
>
> > SELECT
> > ...
> > and
> > (
> > i.indkey[0] = a.attnum
> > or
> > i.indkey[1] = a.attnum
> > or
> > i.indkey[2] = a.attnum
> > or
> > i.indkey[3] = a.attnum
> > or
> > i.indkey[4] = a.attnum
> > or
> > i.indkey[5] = a.attnum
> > or
> > i.indkey[6] = a.attnum
> > or
> > i.indkey[7] = a.attnum
> > )
> > ...
>
> > This was adapted from the psql source. Hope it's what you need.
>
> Actually I think it was borrowed from a very crufty query in the ODBC
> driver. Aside from being ugly, the above-quoted clause is now wrong,
> because indexes can have more than 8 keys since 7.0. This is how ODBC
> finds matching keys and attributes now:
>
> SELECT ta.attname, ia.attnum
> FROM pg_attribute ta, pg_attribute ia, pg_class c, pg_index i
> WHERE c.relname = '$indexname'
> AND c.oid = i.indexrelid
> AND ia.attrelid = i.indexrelid
> AND ta.attrelid = i.indrelid
> AND ta.attnum = i.indkey[ia.attnum-1]
> ORDER BY ia.attnum
>
> which is cleaner since it doesn't assume anything about the max
> number of keys.
>
> regards, tom lane