Base tables where uniqueness is achieved by using only unique indexes

Copy SQL

WITH unique_indexes AS (SELECT 
  n.nspname AS sch,
  c2.relname AS tbl
FROM 
  pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid
  INNER JOIN pg_catalog.pg_class AS c2  ON i.indrelid = c2.oid
  INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
  INNER JOIN pg_catalog.pg_authid AS u ON  n.nspowner = u.oid
WHERE c.relkind = 'i'
AND i.indisunique=TRUE
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')),
tables_with_keys AS (select 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name 
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u','x')  
and c.relkind = 'r')
SELECT A.table_schema, A.table_name 
FROM INFORMATION_SCHEMA.tables A
INNER JOIN INFORMATION_SCHEMA.schemata B
ON A.table_schema=B.schema_name
WHERE A.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR B.schema_owner<>'postgres')
AND (A.table_schema, A.table_name) NOT IN 
(SELECT table_schema, table_name FROM tables_with_keys)
AND (A.table_schema, A.table_name) IN (SELECT sch, tbl
FROM unique_indexes)
ORDER BY A.table_schema, A.table_name;