Query below lists tables and their primary key (PK) constraint names. By browsing list you can spot which tables have and which don't have primary keys.
See also: tables without primary keys.
select tab.table_schema, tab.table_name, tco.constraint_name, string_agg(kcu.column_name, ', ') as key_columns from information_schema.tables tab left join information_schema.table_constraints tco on tco.table_schema = tab.table_schema and tco.table_name = tab.table_name and tco.constraint_type = 'PRIMARY KEY' left 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 tab.table_schema not in ('pg_catalog', 'information_schema') and tab.table_type = 'BASE TABLE' group by tab.table_schema, tab.table_name, tco.constraint_name order by tab.table_schema, tab.table_name
- table_schema - schema name
- table_name - table name
- constraint_name - primary key constraint name
- key_columns - list of PK columns separated with ','
- One row represents one table in a database
- Scope of rows: all tables in a database
- Ordered by schema name, table name
You can see what are the names of PK constraints for each table and which tables don't have PKs at all.
You could also get this
Get this interactive HTML data dictionary in minutes with Dataedo.