How can I list all tables without a primary key?
To list all tables without a primary key, you can use this:
select tbl.table_schema,
tbl.table_name
from information_schema.tables tbl
where table_type = 'BASE TABLE'
and table_schema not in ('pg_catalog', 'information_schema')
and not exists (select 1
from information_schema.key_column_usage kcu
where kcu.table_name = tbl.table_name
and kcu.table_schema = tbl.table_schema)
It's not clear to me if you want to find tables that are not referencing any other tables or tables that are not referenced by other tables. But both things can be obtained by querying information_schema.referential_constraints
in a similar way as the above query.