Re: [GENERAL] how to find primary key field name?
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 = 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
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 = 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 /**/;/**/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
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
FROMINFORMATION_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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
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 = 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
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 = 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
