PostgreSQL query to list all tables without a defined primary key.

README.md

PostgreSQL list tables without defined primary key

This query can be handy for tasks such as setting up a logical replication publication, where by default a table's PRIMARY KEY is used to identify subscriber side rows for update or delete.

By locating tables without a PRIMARY KEY (tisk!), we're able to either update the table schema, or use an alternative replica identity type.

SELECT tbl.table_catalog,tbl.table_schema,tbl.table_name
FROM information_schema.tables tbl
WHERE
  tbl.table_schema NOT IN ('information_schema','pg_catalog') AND
  tbl.table_type = 'BASE TABLE' AND
  NOT EXISTS (
    SELECT FROM information_schema.table_constraints tc
    WHERE
      tc.table_catalog = tbl.table_catalog AND
      tc.table_schema = tbl.table_schema AND
      tc.table_name = tbl.table_name AND
      tc.constraint_type = 'PRIMARY KEY'
  )
ORDER BY tbl.table_catalog,tbl.table_schema,tbl.table_name;