unique indexes

Here is the query from phpPgAdmin that does what you are asking for:

SELECT
ic.relname AS index_name,
bc.relname AS tab_name,
a.attname AS column_name,
i.indisunique AS unique_key,
i.indisprimary AS primary_key
FROM
pg_class bc,
pg_class ic,
pg_index i,
pg_attribute a
WHERE
bc.oid = i.indrelid
and ic.oid = i.indexrelid
and a.attrelid = bc.oid
and bc.relname = '$table'
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
)
ORDER BY
index_name, tab_name, column_name;

This was adapted from the psql source. Hope it's what you need.

-Dan Wilson

----- Original Message -----
From: "Jason Davies" <jason_ddavies@yahoo.com>
To: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, November 19, 2000 8:42 AM
Subject: [GENERAL] unique indexes

Hi,

Thankyou for your help with pg_trigger :)

I am trying to list the indexes for a table. So far I've come up with this

SQL

"Dan Wilson" <phpPgAdmin@acucore.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

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@sss.pgh.pa.us>
To: "Dan Wilson" <phpPgAdmin@acucore.com>
Cc: <jason@netspade.com>; <pgsql-general@postgresql.org>
Sent: Sunday, November 19, 2000 10:14 AM
Subject: Re: [GENERAL] unique indexes