Re: Retrieve the primary key of a table

Alejandro <apinoo@gmail.com> writes:
> Hi. I need to retrieve the primary key of a table and their value.
> I tried with this
> http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns:

> SELECT
>   pg_attribute.attname,
>   format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
> FROM pg_index, pg_class, pg_attribute
> WHERE
>   pg_class.oid = 'TABLENAME'::regclass AND
>   indrelid = pg_class.oid AND
>   pg_attribute.attrelid = pg_class.oid AND
>   pg_attribute.attnum = any(pg_index.indkey);

> But this shows indexes too.

Hm, that query does not do what the page claims.  You need to add
"AND indisprimary".

If you want something more standardized, there are information_schema
views that would help with this problem, too.

            regards, tom lane