I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing 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.
Another one query to get all tables without primary keys:
SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE (table_catalog, table_schema, table_name) NOT IN ( SELECT table_catalog, table_schema, table_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY') AND table_schema NOT IN ('information_schema', 'pg_catalog', 'pgq', 'londiste');
Unfortunately PostgreSQL has no idea what tables are supposed to have FKs. So it is manual work, you will need to look over all the tables and find such tables by yourself.
Here is the query for FKs.
SELECT table_name FROM information_schema.tables WHERE table_name NOT IN ( SELECT distinct table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY' and table_schema ='xxxxx') AND table_schema ='xxxxx'