Re: 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
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