Re: how to find primary key field name? - Mailing list pgsql-general

On 12/10/11 11:54, J.V. wrote:
> If I have a table name, I know how to find the primary key constraint
> name, but see no way to find the primary key field name.
>
> select constraint_name from information_schema.tabale_constraints
> where table_name = <table_name> and constraint_type = 'PRIMARY KEY';
>
> will return the constraint name, but given the table_name and the
> constraint_name, how do I find the database column/field name
> associated with that primary key?
>
> J.V.
>
I think this version is probably more directly useful, and a bit simpler:

SELECT
     ci.relname AS "Index",
     a.attname AS "Primary Key Col"
FROM
     pg_index i
         JOIN pg_class cr ON (cr.oid = i.indrelid)
             JOIN pg_attribute a ON (a.attrelid = cr.oid)
         JOIN pg_class ci ON (ci.oid = i.indexrelid)
WHERE
     i.indisprimary AND
     cr.relname = 'salary' AND
     EXISTS (SELECT 1 FROM unnest(i.indkey) p(c) WHERE p.c = a.attnum)
ORDER BY
     a.attname


Cheers,
Gavin
/**/;/**/