List all primary keys (PKs) in PostgreSQL database

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

See also: tables with their primary keys.

Do you ever feel like him?

Don't worry, we just might have a solution...

Find out what it is

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

Create beautiful and useful documentation of your PostgreSQL

Generate convenient documentation of your databases in minutes and share it with your team. Capture and preserve tribal knowledge in shared repository.

See how it works