Re: 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