Find tables without primary keys (PKs) in PostgreSQL database

Query below lists tables in a database without primary keys.


select tab.table_schema,
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null
order by table_schema,


  • table_schema - schema name
  • table_name - table name


  • One row represents one table in a database
  • Scope of rows: all tables without primary keys in a database
  • Ordered by schema, table name

Sample results

Below is a list of tables in PostgreSQL database without primary keys. Is that a lot? Check out here.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.