Thread: 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
query:
SELECT bc.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum as KEY_SEQ,
ic.relname as PK_NAME
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
WHERE bc.relkind = 'r'
and upper(bc.relname) = upper('tablename')
and i.indrelid = bc.oid
and i.indexrelid = ic.oid
and ic.oid = a.attrelid
ORDER BY table_name, pk_name, key_seq;
I need to extend it slightly to get whether each key is unique or not. Any
ideas on how this might be done?
I would be grateful for any help.
Thanks,
Jason Davies.
=====
Jason Davies,
_ _ _|_ _ _ _ _| _ | www.netspade.com
| |(/_ | _\|_)(_|(_|(/_ | programming tutorials
| | programming community
----------------------- | programming news
__________________________________________________
Do You Yahoo!?
Yahoo! Calendar - Get organized for the holidays!
http://calendar.yahoo.com/
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
> query:
>
> SELECT bc.relname AS TABLE_NAME,
> a.attname AS COLUMN_NAME,
> a.attnum as KEY_SEQ,
> ic.relname as PK_NAME
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> WHERE bc.relkind = 'r'
> and upper(bc.relname) = upper('tablename')
> and i.indrelid = bc.oid
> and i.indexrelid = ic.oid
> and ic.oid = a.attrelid
> ORDER BY table_name, pk_name, key_seq;
>
> I need to extend it slightly to get whether each key is unique or not. Any
> ideas on how this might be done?
>
> I would be grateful for any help.
> Thanks,
> Jason Davies.
>
> =====
> Jason Davies,
>
> _ _ _|_ _ _ _ _| _ | www.netspade.com
> | |(/_ | _\|_)(_|(_|(/_ | programming tutorials
> | | programming community
> ----------------------- | programming news
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Calendar - Get organized for the holidays!
> http://calendar.yahoo.com/
"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 > "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