How Do You Run a SQL Query to Identify the Primary Key of a Table in a PostgreSQL Database?

Problem scenario
You are using PostgreSQL and currently administering a database you inherited from a different company.  You want to determine the primary key of a given table.  How do you identify the primary key of a table?

Modify the query below so you replace "contint" with the name of the table you are interested in.  The result of this SQL query will be the primary key of that table.

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'contint'::regclass
AND    i.indisprimary;

The query was adapted from this link.

Leave a comment

Your email address will not be published. Required fields are marked *