Обсуждение: how to find primary key field name?

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.

On Tuesday, October 11, 2011 3:54:09 pm 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?

Join against constraint_column_usage?:
http://www.postgresql.org/docs/9.1/interactive/infoschema-constraint-column-
usage.html

>
> J.V.

--
Adrian Klaver
adrian.klaver@gmail.com

On 10/11/2011 06:54 PM, 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?

If you query pg_constraint as I showed you before, you can also get
conkey which is an array of smallints pointing at the columns (in
pg_attribute) that form the key.

Joe

On 10/11/2011 6:54 PM, 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  t.table_catalog,
         t.table_schema,
         t.table_name,
         kcu.constraint_name,
         kcu.column_name,
         kcu.ordinal_position
FROM    INFORMATION_SCHEMA.TABLES t
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                 ON tc.table_catalog = t.table_catalog
                 AND tc.table_schema = t.table_schema
                 AND tc.table_name = t.table_name
                 AND tc.constraint_type = 'PRIMARY KEY'
         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
                 ON kcu.table_catalog = tc.table_catalog
                 AND kcu.table_schema = tc.table_schema
                 AND kcu.table_name = tc.table_name
                 AND kcu.constraint_name = tc.constraint_name
WHERE   t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY t.table_catalog,
         t.table_schema,
         t.table_name,
         kcu.constraint_name,
         kcu.ordinal_position;


For multi-column PKs, you'll have to deal with multiple rows (ordered by
"ordinal_position"), or you can array_agg them if you like.


-- Stephen

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
/**/;/**/

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