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.
You might find the following useful:
SELECT
cr.relname AS "Table",
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_namespace n ON (n.oid = cr.relnamespace)
JOIN pg_attribute a ON (a.attrelid = cr.oid)
JOIN pg_class ci ON (ci.oid = i.indexrelid)
WHERE
i.indisprimary AND
n.nspname = 'public' AND
EXISTS (SELECT 1 FROM unnest(i.indkey) p(c) WHERE p.c = a.attnum)
ORDER BY
cr.relname,
a.attname
/**/;/**/
Cheers,
Gavin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
