Querying PostgreSQL to Find The Primary Key of a Table

Oct 26 2015

Recently I found myself trying to programmatically find out which columns on a database table were acting as the primary key. It turns out that there is a relatively straightforward (and standard!) way of doing this.

Postgres provides the SQL-compliant Information Schema database, which contains useful table information, including indices. As I understand it, much of the information you're looking for can be obtained through this schema. And in some ways the table structure (and subsequent queries) are much simpler.

The fields on the information_schema.key_column_usage table look like this:

# \d information_schema.key_column_usage
               View "information_schema.key_column_usage"
        Column             |                Type                | Modifiers
-------------------------------+------------------------------------+-----------
 constraint_catalog            | information_schema.sql_identifier  |
 constraint_schema             | information_schema.sql_identifier  |
 constraint_name               | information_schema.sql_identifier  |
 table_catalog                 | information_schema.sql_identifier  |
 table_schema                  | information_schema.sql_identifier  |
 table_name                    | information_schema.sql_identifier  |
 column_name                   | information_schema.sql_identifier  |
 ordinal_position              | information_schema.cardinal_number |
 position_in_unique_constraint | information_schema.cardinal_number |

A simple query lists the key columns:

SELECT constraint_name, table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 'organizations';

Generates:

              constraint_name                  |  table_name   |              column_name           | ordinal_position
---------------------------------------------------+---------------+--------------------------------+------------------
 organizations_pkey                                | organizations | organizations_id               |                1
 organizations_organizations_type_id_fkey          | organizations | organizations_type_id          |                1
 organizations_organizations_church_status_id_fkey | organizations | organizations_church_status_id |                1
 organizations_organizations_ethnic_id_fkey        | organizations | organizations_ethnic_id        |                1
 organizations_organizations_language_id_fkey      | organizations | organizations_language_id      |                1
(5 rows)

The information_schema.table_constraints table lists all of the constraints (including keys) by table. Using that to JOIN on, it is also fairly easy to query particular kinds of keys. For example, here's how to find out the primary key by table name:

SELECT c.column_name, c.ordinal_position
FROM information_schema.key_column_usage AS c
LEFT JOIN information_schema.table_constraints AS t
ON t.constraint_name = c.constraint_name
WHERE t.table_name = 'organizations' AND t.constraint_type = 'PRIMARY KEY';

Output:

   column_name    | ordinal_position
------------------+------------------
 organizations_id |                1

The disadvantage of this approach is that it requires a little more work than a simple \d TABLENAME. But the advantages are (a) that with straightforward queries you can get useful information, and (b) because the Information Schema is standardized, there's at least some hope that the query will be portable across other database engines. (A quick check shows that both MySQL and SQLServer implement Information Schema).