Re: how to find primary key field name?

[Stephen Cook <sclists@xxxxxxxxx>]

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general