Talk:Retrieve primary key columns

From PostgreSQL wiki

Jump to: navigation, search

Query shows all indexes?

With my PostgreSQL 8.3.7, the query shows all indexes and their attributes, e. g.:

tim=# SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'phonenumbers'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey);
   attname   |      format_type
 id          | integer
 phonenumber | character varying(20)
 id          | integer
(3 Zeilen)

for a primary key on (id, phonenumber) and another index on (id). --Tim 04:05, 29 March 2009 (UTC)

Personal tools