WITH tables_with_unique AS (SELECT
nspname AS schema_name,
c.relname AS table_name
FROM pg_constraint o INNER JOIN pg_class c on c.oid = o.conrelid
INNER JOIN pg_namespace n on n.oid=c.relnamespace
WHERE o.contype IN ('x','u') 
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT 
  n.nspname AS table_schema,
  c2.relname AS table_name
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
  INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid    
WHERE c.relkind = 'i'
AND i.indisunique=TRUE
AND a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')
AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid AND o.contype IN ('u','p'))),
tables_with_only_surrogate AS (SELECT c.table_schema, c.table_name , c.column_name
FROM information_schema.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR c.is_identity='YES')
AND c.is_nullable='NO'
AND (table_schema, table_name) NOT IN (
SELECT table_schema, table_name
FROM tables_with_unique))
SELECT table_schema, table_name 
FROM tables_with_only_surrogate AS twos
WHERE  (SELECT Count(*) AS cnt FROM information_schema.columns AS c WHERE c.table_schema=twos.table_schema AND c.table_name=twos.table_name)>1
ORDER BY table_schema, table_name;