Thread: [Fwd: Names of columns used by primary key]

Question for pqsql-php mail list:
How can I determine the names of the columns that make up the primary
key in a postgresql table?
Your help would be appreciated.
Question for pqsql-php mail list:
How can I determine the names of the columns that make up the primary key?
Your help would be appreciated.

on 8/16/02 12:32 PM, kgordon@paradise.net.nz purportedly said:

> Question for pqsql-php mail list:
> How can I determine the names of the columns that make up the primary
> key in a postgresql table?
> Your help would be appreciated.

What you need is found in the system catalogs.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

This query should help you out:

SELECT
    ic.relname AS index_name,
    bc.relname AS tab_name,
    ta.attname AS column_name,
    i.indisunique AS unique_key,
    i.indisprimary AS primary_key
FROM
    pg_class bc,
    pg_class ic,
    pg_index i,
    pg_attribute ta,
    pg_attribute ia
WHERE
    bc.oid = i.indrelid
    AND ic.oid = i.indexrelid
    AND ia.attrelid = i.indexrelid
    AND ta.attrelid = bc.oid
    AND bc.relname = '[table_name]'
    AND ta.attrelid = i.indrelid
    AND ta.attnum = i.indkey[ia.attnum-1]
ORDER BY
    index_name, tab_name, column_name

Of course, be sure to change the "[table_name]".  Then all you have to do is
check if primary_key is 't' and then get the column name.

-Dan

Quoting Kevin Gordon <kgordon@paradise.net.nz>:

> Question for pqsql-php mail list:
> How can I determine the names of the columns that make up the primary
> key in a postgresql table?
> Your help would be appreciated.