Query below lists all primary keys constraints (PK) in the database.

See also: tables with their primary keys.

Query

select kcu.table_schema,
       kcu.table_name,
       tco.constraint_name,
       string_agg(kcu.column_name,', ') as key_columns
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
group by tco.constraint_name,
       kcu.table_schema,
       kcu.table_name
order by kcu.table_schema,
         kcu.table_name;

Columns

• table_schema - PK schema name
• table_name - PK table name
• constraint_name - PK constraint name
• columns - list of PK columns separated with ','

Rows

• One row represents one primary key (table) in a database
• Scope of rows: all PK constraints in a database
• Ordered by table schema, table name

Sample results